additionalScope parameters not passing through to my query

App Building
1

I have a js query that is triggered when a cell in a table is edited.

// Initialize the variables
let routeId = null;
let jobId = null;

// Check if changesetArray exists and is not empty
if (tableLocations.changesetArray && tableLocations.changesetArray.length > 0) {
  // Get the Route Number and Job ID from the selected row in tableLocations
  const routeNumber = tableLocations.changesetArray[0].routeId;
  jobId = tableLocations.changesetArray[0].id;
  
  // If the routeNumber is not "Unassigned" or null, set routeId to the routeNumber
  if (routeNumber !== 'Unassigned' && routeNumber !== null) {
    routeId = routeNumber;
  }
}

// Trigger the SQL query if jobId is available
if (jobId) {
  updateRouteId.trigger({
    additionalScope: {
      routeId: routeId,
      jobId: jobId
    },
    onSuccess: function(data) {
      console.log('Update successful:', data);
    },
    onFailure: function(error) {
      console.error('Update failed:', error);
    }
  });
} else {
  console.error('No valid changeset available.');
}

When I hover over the additional scope, I can see the values
chrome_p8wnFPJLJU

Here is my update query:

-- Temporary debug query
SELECT {{ routeId }} AS passedRouteId, {{ jobId }} AS passedJobId;

-- Actual update query
UPDATE public.jobs
SET route_id = 
  CASE 
    WHEN {{ routeId }} IS NULL THEN NULL
    ELSE (
      SELECT id 
      FROM public.routes 
      WHERE route_number = {{ routeId }}::integer
    )
  END
WHERE id = {{ jobId }}::uuid;

Additional scope is set:
chrome_atLpe5L7S7

When I try to update the cell the query throws the following error:
could not determine data type of parameter $1

In the state, I do not see any data:
chrome_MWnjLpTLpC

What am I doing wrong?

2

Hi @Shawn_Optipath,

Just to confirm that the id and routeId are available in your changesetArray, you have set the table to "Include full rows in changeset Array" correct?

image

You should be able to do some additional debugging by adding something like: console.log({jobId, routeId}) right before if(jobId) and then checking the console to ensure those values are populated as expected.

3

sometimes I have to use '{{ Parameter1 }}' in the query. I forget wha the cause is and this might not even be your issue, but hopefully we get lucky.

4

Hi @MikeCB ,

Yes, the changeset it available:
chrome_so6hhmZmIq

I can already see the data for both parameters being passed in the trigger:
VQmUQrErCN

you have set the table to "Include full rows in changeset Array" correct?

I have not but i don't need it in this case. Very handy though. I didn't know of this setting :slight_smile:

5

I tried '{{ Parameter1 }}' in all 3 areas. Thanks for the idea though

6

oh I mean with the ' ' around the { }, not 'Parameter1'... for u '{{ routeId }}' and '{{ jobId }}', if you're using copy/paste be sure to get the single quotation marks

7

Yes, that is what I understood. I tried '{{ routeId }}' and '{{ jobId }}' as you mentioned.

8

bummer, then i think the problem is ::uuid, try this:

WHERE id::text = {{ jobId }}::text

additional scope variables are all passed as string, so even if on the calling side it's UUIDv4 or something it's automatically cast to string before it's used in the call to the query.

9

That didn't work either. I did find a way to use a query with a js query in the meantime. Would have been nice to know how how to pass the variables though. Even with the js query it seems like overkill.

Query:

UPDATE public.jobs
SET route_id = 
  CASE 
    WHEN COALESCE({{ transformRouteID.data }}, NULL) IS NULL THEN NULL
    ELSE (
      SELECT id 
      FROM public.routes 
      WHERE route_number = {{ transformRouteID.data }}::integer
    )
  END
WHERE id = {{ tableLocations.changesetArray[0].id }}::uuid;

Transformer:

// Check if changesetArray is null or empty
if (! tableLocations.changesetArray  ||  tableLocations.changesetArray.length  === 0) {
  return null; // Early return to prevent further processing
}

// Get the Route Number from the selected row in tableLocations
const routeNumber =  tableLocations.changesetArray[0].routeId ;

// If the routeNumber is "Unassigned", return null, otherwise return the routeNumber itself
const routeId = (routeNumber === 'Unassigned' || routeNumber === null) ? null : routeNumber;

// Return the result
return routeId;
10

Glad you have a path forward!

I added ::integer to the first param, and now this syntax works fine for me with Retool DB :thinking: it could be different for other SQL types though

1 Like