Forum Discussion

AlexGale's avatar
AlexGale
Qrew Captain
7 years ago

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

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 ...
APIs and custom code
App builders
QuickBaseCoachD's avatar
QuickBaseCoachD
Qrew Captain
7 years ago
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.


AlexGale's avatar
AlexGale
Qrew Captain
7 years ago
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.