Use API to copy selected field values from table "A" to fields in table "B" only if(checkbox=true)

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered
So I have a table of "RFQ's" that get filled out by any random employee. Once the RFQ is created, it gets forwarded to a specific user (Expediter), who will then either create a "Purchase Order" or "Work Order" for each line item listed on the RFQ form.


I want to streamline the process by enabling the Expediter to check a checkbox next to each line item, and click a formula URL button that will then copy the selected lines (several fields per line) to a different table (either PO or WO.) All three forms are configured similarly, with similar fields on each one.


I was able to get the "header" information to copy over, using the following on the RFQ form:

URLRoot() & "db/work order db" & "?act=API_GenAddRecordForm"
& "&_fid_32=" & [Title]
& "&_fid_162=" & [Related User]
& "&_fid_11=" & [Project]
& "&_fid_33=" & [Required By]
& "&_fid_21=" & URLEncode("Deliver to " & [Deliver To])


Now, essentially, i want to add something like,
If([checkbox1]=true,
& "&_fid_1=" & [qty1]
& "&_fid_2=" & [part number1]
& "&_fid_3=" & [desc1],)
If([checkbox2]=true,
& "&_fid_4=" & [qty2]
& "&_fid_5=" & [part number2]
& "&_fid_6=" & [desc2],)

.....and so on.

-Now-
Here's where it gets a little wild...
Lets say checkbox1 (line item 1) and checkbox3 (line item 3) on the RFQ form are true,
Is there a way to then copy the values from the fields on line 3 (qty3, part number3, desc3) to the next empty line of the WO form (fid 4, 5, 6)?

I attached a snip of the RFQ form to show how its built as it doesn't use child tables for line items.
Photo of Matthew

Matthew

  • 60 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,316 Points 50k badge 2x thumb
& if([checkbox1], "&_fid_1=" & [qty1])
& if([checkbox1], "&_fid_2=" & [part number1])
& if([checkbox1], "&_fid_3=" & [desc1])

& If([checkbox2], "&_fid_4=" & [qty2])
& If([checkbox2], "&_fid_5=" & [part number2])
& If([checkbox2], "&_fid_6=" & [desc2])



But closing up the empty lines would take a lot of code
Photo of Matthew

Matthew

  • 60 Points
Thanks for the first part,

How much code are we talking? I'd imagine it could be done pretty easily if each "line item" was a record in a child table. But changing the structure of our PO and WO forms would take weeks.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,316 Points 50k badge 2x thumb
Hard coded Line item tables are always bad news. But typically get build because the App builder did not really know about child tables for their first few apps.  So yes, child tables would open up many more possibilities.

My suggestion is to copy the lines across into their respective slots, so line 10 lands in line 10.  But then on the Target app, make a set of formula fields which are just mirrors of the real fields.  Then use form setup and form rules to show the real fields in edit mode, the formula mirror fields in view mode and then 10 form rules for 10 lines to hide the formula fields if they are blank.
Photo of Matthew

Matthew

  • 60 Points
Well we originally went with the hard coded table because the form is exported to an exact form for printing.

I see how that solution could get us by, but would there be a way to stack url formulas where it checks to see the next blank line in the target form? Something like
if(fid1(target)=blank, "_fid_1="&[qty1], fid4=blank, "_fid_4="&[qty1], etc..
if(fid2=blank, "_fid_2="&[dwg1], fid5=blank, "_fid_5="&[dwg1], and so on...
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,316 Points 50k badge 2x thumb
It's probably possible, but I think that it would be a large formula and would probably take by 4 hours to figure it out and get it coded.  I thought about it a bit while I was rollerblading and could not see an "elegant" solution.  it sounds like the user could choose any combination of the say 10 lines on an RFQ, so for example they may chose line 7 or just the even lines or just odd lines or just lines 5 and 10.  So i would have to really sit myself down and concentrate on how the formula could work.

I think it is possible, but its just not a casual undertaking and would need to be handled outside this forum as consulting work.  If you want you could post that as a new Question on the forum and see if any one else has ideas which are less brute force.  It may also be able to be dine more elegantly in script, but then you have a non-native solution and I personally try to avoid this in my apps where I can.  The infamous Dan Diebolt who haunts this forum may have an elegant solution using javascript and you could contract with him to get it done.
Photo of Matthew

Matthew

  • 60 Points
Yes, the user could select any random combo of the items on an RFQ. And I understand what you mean with an "elegant" solution. I think I might try to coax Dan Diebolt out with this question, but alternatively, lets say I was able to change the structure of all three forms to use child tables, how then would I use an exact form to display data on the embedded table on each form?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,316 Points 50k badge 2x thumb
I cannot answer your question about using the native Exact forms feature to embed a child table.  It will definitely do that, but i have found that my clients all prefer to have an actual .docx or .pdf file created as a file attachment so I steer them to the Juiced Exact forms Plus product.  As such, I have not put the required effort into learning the setup for the native QuickBase Exact Forms, so if you need help with that you would need to "RTFM" as they say or post a new forum question.

But if they were child tables, then it would be way easier to copy child records from one table to another.   That would be a separate topic if you do decide to do that, but I do that all the time for my clients.  

There is thread here where after a long discussion there is a recommended answer which uses the technique I would use to copy child records from one table to another.  https://quickbase-community.intuit.com/questions/1202665-merge-table-how-to-automate-merging-of-records
Photo of Matthew

Matthew

  • 60 Points
I will look into your references. Thank you!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,894 Points 20k badge 2x thumb
>I think I might try to coax Dan Diebolt out with this question ...

I think you should add child tables. if you pile on a bunch of "complex formulas" you are just going to delay the ultimate resolution of the problem - adding child tables for the line items.