Discussions

Expand all | Collapse all

Copy a child record that contains a Multi-Line Text Field.

Printmark Printmark10-21-2017 16:53

  • 1.  Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 16:32
    I working on a quote form and need to ability to copy a child record on the quote.
    (Quote being the parent, and Details being the child records).

    I can get it working just fine with the following URL Formula:

    var text URL= URLRoot() & "db/" & Dbid() & "?a=API_AddRecord"
    & "&_fid_15=" & [Related Sales order]
    & "&_fid_13=" & URLEncode([Related Product])
    & "&_fid_7=" & URLEncode([Product Description2]) //this is a lookup field
    & "&_fid_8=" & [Product Quantity]
    & "&_fid_9=" & [Product Unit Price]
    & "&_fid_24=" & ToNumber([Taxable Item]);

    "javascript:" &
    "$.get('" & 
    $URL & 
    "',function(){" &
    "location.reload(true);" &
    "});" 
    & "void(0);"

    (FID #7 is the ID for [Product Description] a multi-line text box).

    [Product Description2] is a lookup field that pulls in a default product description from a products table.  Using form Rules, I then copy this value to a field called [Product Description] which is multi-line text box.  This allows the sales reps to edit the product description as needed, and this is what is displayed on the quote.

    What I really need is this URL Formula to work:

    var text URL= URLRoot() & "db/" & Dbid() & "?a=API_AddRecord"
    & "&_fid_15=" & [Related Sales order]
    & "&_fid_13=" & URLEncode([Related Product])
    & "&_fid_7=" & URLEncode([Product Description]) // this is a multi-line text box.
    & "&_fid_8=" & [Product Quantity]
    & "&_fid_9=" & [Product Unit Price]
    & "&_fid_24=" & ToNumber([Taxable Item]);

    "javascript:" &
    "$.get('" & 
    $URL & 
    "',function(){" &
    "location.reload(true);" &
    "});" 
    & "void(0);"

    But when I change [Product Description2] to [Product Description] - The URL Formula no longer works (no errors, just don't work).

    The only reason I can think of is maybe it is because it is a Multi-Line Textbox.
    Any Ideas??

    Thanks

    Mark


  • 2.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 16:38
    That javascript refresh is super handy, but it also hide error messages.

    To debug,

    Do this and then it will expose the success or failure message.  

    var text URL= URLRoot() & "db/" & Dbid() & "?a=API_AddRecord"
    & "&_fid_15=" & [Related Sales order]
    & "&_fid_13=" & URLEncode([Related Product])
    & "&_fid_7=" & URLEncode([Product Description]) // this is a multi-line text box.
    & "&_fid_8=" & [Product Quantity]
    & "&_fid_9=" & [Product Unit Price]
    & "&_fid_24=" & ToNumber([Taxable Item]);

    $URL

    //"javascript:" &
    //"$.get('" & 
    //$URL & 
    //"',function(){" &
    //"location.reload(true);" &
    //"});" 
    //& "void(0);"




     


  • 3.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 16:45
    This is the result.

    <qdbapi>
    <action>API_AddRecord</action>
    <errcode>0</errcode>
    <errtext>No error</errtext>
    <rid>68</rid>
    <update_id>1508604229225</update_id>

    </qdbapi>


  • 4.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 16:49
    OK, so put back the formula the way it was.  I thought you meant that it did not create the record.  You are saying that it does create the record, but the fid 7 is blank.

    When you view (not edit) the source record that you are copying, can you tell me what text is on the field [Product Description]?  ie does it in fact have text in it?   


  • 5.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 16:53
    Here is picture of the table with examples.


  • 6.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 17:00
    Hmmm, it should work.
    Does it also fail on the record that says UPS Ground?


  • 7.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 17:03
    That line copies just fine, which is what lead me to believe it had something to do with the multiple lines.  (thanks for you help with this)


  • 8.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 17:08
    Hmmm, so the issue is data dependent.  The URLEncode is supposed to take care of that problem of special characters.  Is is just = equals signs as special characters that you have on the text boxes which fail?  It was hard to see in the screen shot. I guess you have  () characters too.

    Also when you have the formula pointing back to [Product Description2] and that field has = equals signs, does it fail or succeed?


  • 9.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 17:15
    Special characters commonly used are = : ( ) /
    and carriage return.

    I'll work on a process of elimination to see if I can narrow it down.

    [Product Description2] the lookup is just the product description only and would never contain special character (with the exception of maybe / ).


  • 10.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 17:22
    It is the multi-line that is creating the issue.  As long as I have just one line of text, it works, when I add a second line, it fails (it doesn't create the record at all).

    In addition, if I add the = character to a single line, it fails to display the description, but it does create the record.


  • 11.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 17:25
    I'm running out of ideas.

    out of desperation you could try this
    & "&_fid_8=" & ToText([Product Quantity])
    & "&_fid_9=" & ToText([Product Unit Price])
    & "&_fid_24=" & Totext(ToNumber([Taxable Item]));


  • 12.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 17:28
    Multiline parameters should not be passed in the query string of GET requests. There can be touchy issue even if you URLEncode the query string parameter. The most comprehensive solution is to use script.

    There probably is a native workaround but I would have to see the absolutely most simplistic example that fails. All the other fields and the context of your full application are just obscuring the core problem. Create the simplest possible example (new application , one table, minimal number for fields and test data) that demonstrates the same problem.


  • 13.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 17:31
    ToText made no difference in the behavior.

    Is there another way to copy a child record?  using GenCopyRecord ?

    It works great if I go into Form View, the select More>copy this record.  I was just trying to get that functionality in a button. 


  • 14.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 17:34
    The most flexible solution is to use script. You can still get your button to invoke the script. You will be spinning your wheels for a long time playing with native formulas.


  • 15.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 17:43
    I would try this native solution first before having to give up or using script.

    You could try this

    var text URL= URLRoot() & "db/" & Dbid() & "?a=API_CopyMasterDetail&destRID=0&sourcerid=" & [record ID#]&copyfid=6;

    $URL


    Leave out the javascript refresh for now until we see if this works.

    This is going to copy the parent and the children, but your item presumably has no children.  (We can adjust the formula later to ensure it does not copy future children in case your item line records get pregnant and you decide to make it a Parent in the future.


    You will need to have a dummy field to use in place of copyfid=6.  Just make a blank data entry field called [Required for CopyMasterDetail] and change the 6 to that fid.

    All fields will be copied, it it works.


  • 16.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 17:56
    I'm getting a syntax error that function copyfid must begin with left parenthesis.


  • 17.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 17:57
    var text URL= URLRoot() & "db/" & Dbid() & "?a=API_CopyMasterDetail&destRID=0&sourcerid=" & [record ID#] & "&copyfid=29";


  • 18.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 18:05
    Here are the results

    <qdbapi>
    <action>API_CopyMasterDetail</action>
    <errcode>0</errcode>
    <errtext>No error</errtext>
    <parentrid>85</parentrid>
    <numCreated>1</numCreated>

    </qdbapi>


  • 19.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 18:07
    It copied the Muti-line text and all special characters... WooHoo..


  • 20.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 18:12
    Hurray for native.

    OK, so  we have our breakthrough.  You can re-implement that javascript refresh code.

    So now, will you ever make that item line a Parent of it's own children?  If so, that will, by default, copy all child records.  Let me know if that is even a remote possibility and if so I can suggest a fix for that.  We will create a dummy Report Link field to define the child records to be copied.


  • 21.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 18:15
    No chance of ever having child records associated with the quote details, so I don't think we need to go that deep.  I put the javascript refresh back in there and all is working as expected.

    Thanks for you help on this.. I really appreciate it.

    Mark


  • 22.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 18:21
    Sometimes in Quick Base we hit a brick wall, don't really understand why, but can find another way around the wall.

    So we just leave the brick wall mystery to another day or maybe never solved and we move on to the next item on the todo list.


  • 23.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 18:21
    >Hurray for native.

    That isn't a pure native solution - you have JavaScript in there

    "javascript:" &
    "$.get('" & 
    $URL & 
    "',function(){" &
    "location.reload(true);" &
    "});" 
    & "void(0);"

    Hurrah for script!


  • 24.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 18:31
    Today was one more brick in the wall for me, it is a good feeling.. thanks again!.

    Now onto the next brick (resorting of the line items - drag and drop would be great, but thinking a numbering solution might be my only option).  My trouble is they appear in a different order when using exact forms. - but this is another post topic I have to research).


  • 25.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 18:31
    Today was one more brick in the wall for me, it is a good feeling.. thanks again!.

    Now onto the next brick (resorting of the line items - drag and drop would be great, but thinking a numbering solution might be my only option).  My trouble is they appear in a different order when using exact forms. - but this is another post topic I have to research).


  • 26.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 18:33
    Yes, I do love that javascript refresh code snippet.  I use it every day in new formula buttons.


  • 27.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 18:38
    For sorting you may need to let the user provide an override sequence numeric field to sort on.  Alas,  no native drag and drop, but I seem to recall Dan had a drag 'n drop demo working with, yes, script. 


  • 28.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-21-2017 18:49
    Yeah you can drag your records around and sort them in to a new order:

    QuickBase Bloggers ~ Reorder Bloggers
    https://haversineconsulting.quickbase.com/db/bmguzsp87?a=q&qid=1

    Pastie Database
    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=543

    Here I am in the midst of demoting Kirk to second fiddle:



  • 29.  RE: Copy a child record that contains a Multi-Line Text Field.

    Posted 10-22-2017 00:27
    Cool... I looked at the pastie, and it all looked a bit cryptic to me.  I'll have to do my homework to figure out how all that works. Thanks for sharing.