Recommended way to lookup a record ID by a query

Hi there, just coming from a lively discussion with the support team… but still a bit lost. What is the recommended best-practice how to query for a single recored matching certain parameters in a table and to load that matched record into a record placeholder, if any is returned?

I tried by

  1. Setting a variable as a query parameter on step enter
  2. using this query variable in a table query
  3. creating a MODE aggregate to fetch the returned table record ID of that query, if present
  4. using a trigger after the variable edit to load the ID returned from an aggregate on the query into a record placeholder.

Outcome: Not stable. Sometimes the step stalls because the ID returned form the aggregate query comes back too late.

Support tells me there is some kind of async fetch “problem” with table aggregations… and hence the returned ID may or may not yet be present at the time it is needed.

Hey @sebme -

I assume you are running into this async behavior when using that aggregation in a custom widget (as a prop). Is that the case?

Normal Triggers will always wait for aggregations to update before using that value. Custom widget props should always be responsive to changes in their props, and this is especially the case for props that are aggregations because often a CW on a step will load before its aggregation returns a value.

//Do something anytime a prop value changes

getValue('My Prop', (internalVariable) => {
    update(internalVariable);
});

On top of this, I often also verify that the aggregation is non-null before using it.

Your methodology for getting ids on the table side is acceptable, but I usually use a slightly different approach.

  1. Setup a query where the limit field is set to 1. All other filters and sorting drive what record is that single record.
  2. Aggregation to return unique values of id column.

Hope this helps,
Pete

@Pete_Hartnett : No, no custom widget involved here… just a plain query/step trigger thing.

Regarding what you show below: Yes, this is more or less what I am doing right now… except I use the MODE aggregation. But with the same limit etc.

Use case:

When reaching the required step, query the BOM of the incoming order for a specific item ID and read out the configuration string that is contained in the value field of the identified table record.

@sebme It seems like this is what you are doing… but just to be sure, here is a quick 2 minute video on how I recommend users do this. I haven’t had issues with this method, although I have noticed that in some conditions I will need to act upon the table aggregation in order make it refresh. For example, if I am just visualizing it and want it to be updated I will store it somewhere (data manipulation>store). This seems to override the default autorefresh duration.

Hi there,

yes, this is exactly what has been setup here. But the culprit seems to be that the query update is triggered during a step change where on step entry the search parameter of the query is updated and in a later trigger the resulting record is supposed to be automatically fetched to retrieve the result for some further process…

It is exactly during this “automated” processing that we seem to be experiencing a race condition in which there is nothing guaranteeing that the fetched record ID is present at the time it is needed to fetch the actual record.

Looks like for these things to work in a stable manner a connector function is mandatory - because it is currently the only way to guarantee that data is there before the trigger proceeds… at least so it seems…?

This will have important design implications… and sadly means I will probably have to change quite a few things in my app now as a result of it :face_with_thermometer:

@sebme Could you DM @Pete_Hartnett or me the link to the app/step? We could check it out and see if there is a way to refresh the table aggregation on step change so that you don’t need to use the API. We could also hop on a quick call if you’d like.

I have created a small demonstrator app to show what i tried so far. Our test app was lately moved to the European servers and everything runs much much faster now. BUT, this increased speed clearly has broken the approach chosen in the demonstrator entirely. So right now, the connector path is the only feasible.

Looking forward to learn about an alternative, if possible.