Data Table search on a Data Relations field

UI Builder
1

I am trying to discover if a record already exists in a DB Table. The where clause is "clientid=180 AND vendorid=3 and billdate=‘01-31-2024’ ". The values for clientid and vendorid are in fields defined as data-relations. What is the proper way to retrieve this data request?
My codeless:

image
image1019×638 37.7 KB

My table:
image

2

I recommend checking the list length returned from the Load Table objects block. If the length is 0, it means record(s) could not be found.

Before you proceed with programming logic, testing your where clause first would be extremely important. The Data screen includes the REST CONSOLE tab that makes it easy to check the validity of your where clause. Run a query with your where clause to see if it is working.

Some other notes:

  • Referencing columns from a related table should work like this: relationColumnNameFromTheParentTable.columnNameFromTheRelatedTable. I see your column names are clientid and vendorid. It means in the where clause they should be set as clientid.columnNameFromClients, etc. This is the part that you can flush out by testing your where clause as suggested above.
  • It is unclear why you set the related depth to 1 if all you need to do is check if a record exists. Setting relation depth to 1 (or any other number) makes the query more complex and less performing but does not impact the final outcome (which is checking if the data is there)
  • if a column in the database has the type of STRING, the values for the query in the where clauses need to be enclosed in single quotes. I am not sure what the data type of the vendorid column is, but if it is STRING, the where clause needs to be modified to enclose the value in single quotes.

Regards,
Mark

3

Mark,

  • I am using [relations depth] to get the value of the search keys to show in the results.
  • I have adjusted my where clause to be [clientid.clientid=xxx AND vendorid.vendorid=yyy AND (billdate>=‘datevalue 00:00:00’ AND billdate<=‘datevalue 23:59:59’)].

I actually had 2 issues happening here, the referencing column specification and then the datevalue did not work correctly until I specified a range.

Thanks for the pointers.

1 Like