Can I use API_DoQuery to automatically relate records to different parents?

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • Answered
Hi, 

I have a table called ORDERS. When an order is created I want the system to find a record from a separate table, RATES, which matches the values of several fields from that order. I then want to make that rate a parent record of the order. 

At first I thought I could do this using a webhook that triggers an EditRecord action whenever an order is created, but I need to point it at the correct rate.

I have a fairly limited knowledge of the API, but it seems like I should be looking at a DoQuery to find the correct rate. I think I get how I would structure the query itself to find the correct record, but I don't know how to take that response and then use it in an Edit action. 
Photo of Alex Gale

Alex Gale

  • 1,462 Points 1k badge 2x thumb

Posted 7 months ago

  • 0
  • 1
Photo of Rob White IV

Rob White IV

  • 1,070 Points 1k badge 2x thumb
I don’t know if you are comfortable with JavaScript, but if so, there is a pretty good example of that use case in the example use code for this QuickBase Module.

https://www.npmjs.com/package/quickbase

Maybe that will help you.

~Rob
Photo of Alex Gale

Alex Gale

  • 1,462 Points 1k badge 2x thumb
Thanks for the link Rob. I'm familiar enough with javascript to get the general gist of what's going on in that code. What I don't understand is how I fit that sort of thing into my app. Until now I've been using the API in a fairly limited fashion, with simple webhooks and url fields and I just don't get how I go from there to using that javascript code in any practical fashion. 
Photo of Rob White IV

Rob White IV

  • 1,070 Points 1k badge 2x thumb
I have found luck with code pages. Sorry I can’t expand. I’m on my phone. If no one jumps in I’ll elaborate later when I’m back near my station.
Another approach which would be totally automatic and zero code at all, would be to set the Key field of your rates table to be a concatenated field of the fields which make the rate unique.

You can either have a scalar data entry field as the Key field and a form rule to mirror a formula field into that scalar data entry field, or if you are not doing imports, in fact you can set a formula field to be a key field via an API typed into the browser URL.  

Surprisingly, while undocumented, you may have a formula field be a Key field.  The only problem I have had is when importing into the table.


Then you make a relationship down to your detail table based on a formula field to construct the a value matching the Key field of the Rates Table.
Photo of Alex Gale

Alex Gale

  • 1,462 Points 1k badge 2x thumb
I appreciate the tip. I may end up doing just this but it's not the ideal solution. The app is pretty built-out and these particular tables see pretty regular use. From experience, changing a key field can end up being a big hassle.
Photo of Alex Gale

Alex Gale

  • 1,462 Points 1k badge 2x thumb
Your tip gave me an idea. I created formula fields on both the order and rates tables to concatenate the different field values that need to match. I then created a report link field on the orders table which looks for a rate using those formula fields. 

From there, I created a simple QB action on the orders table which targets the correct rate using the report link and copies the order ID# into a numeric field on that rate. 

Changing that numeric field triggers a second action on the rates table which then modifies the order matching that numeric field to have the rate as a parent. 

It's awkward that it requires two actions but it works. Thanks for your help. 
.. so do you kick off the Acton by pushing a button?
Photo of Alex Gale

Alex Gale

  • 1,462 Points 1k badge 2x thumb
No, the first action triggers when an order is added.