Skip to main content
M
michals0005
December 19, 2022
Question

Query record without a relationship

  • December 19, 2022
  • 6 replies
  • 0 views

Hi guys.

I'm new to complex queries with records and I've got a question about how to execute a query based on records when there is no key based relationship between two tables.

Imagine a situation where there are two tables

1. Executed jobs table

Jobs      
Id Summary Employee Hours Timestamp
1 Pipe change johnb 3 10/12/2022 12:34
2 Cleaning andyw 5 08/12/2022 10:22
3 Installation johnb 2 03/12/2022 09:12
4 Destruction johnb 4 02/12/2022 18:34

2. Hourly rates of employees

Rates        
Id Employee From To HourlyRate
1 johnb 01/02/2021 30/11/2021 12
2 andyw 01/03/2021 31/01/2022 8
3 johnb 01/12/2022   14
4 andyw 01/02/2022 31/12/2022 11
5 andyw 01/01/2023   14

In result I need to list executed jobs with their cost. But the join between these tables is based on employee name and timestamp of job fitting into rate of employee at job execution time. Using SQL I could perform a join on multiple fields, something like

SELECT jobs.id, jobs.summary, jobs.employee, jobs.timestamp, (jobs.hours * rates.HourlyRate) as jobCost
FROM jobs
LEFT JOIN rates ON (jobs.employee = rates.employee AND jobs.timestamp BETWEEN rates.from and rates.to)

How to do that in the queryRecordType world?

I could do a query, and then a loop and for each job record execute a calculation with retrieving rate information. But would result in multiple SQL statements, and I'm guessing there are better ways for it, right?

And just for the sake of it, it's not that I have a small table of 4 jobs and 5 rates for 2 employees [emoticon:c4563cd7d5574777a71c318021cbbcc8] This is just an example representation of an obviously larger set of data.

Help will be much appreciated [emoticon:c4563cd7d5574777a71c318021cbbcc8]

6 replies

konduruc733182
konduruc733182
January 3, 2023

Hello michals0005,

You can create do that using a!queryRecordType().  Create two records for each of your source.

Let's say you are using it in a dropdown field. 

From my understanding you want to display the Employee Name from Executed jobs table and on selection you would like to show data from Hourly rates of employees. 

Create a query for your Executed jobs record type and make sure you get only the data. Create another record type query for your Hourly rates of employee record and make sure you have a filter for the employee field. 

you can add filters and give the operators such as between.

You can now call your Executed Jobs record query in a local and try indexing the name of the employee as your dropdown Choice values and Choice labels. 

consider another local variable for the selected employee name.

Call your Hourly rates of employee in a local variable and in that rule you need to pass the variable where you are saving the dropdown selection value. You can add multiple filters to your query and have dropdowns for passing all the values. 

a!localvariables(
local!employeeTable: rule!yourEmployeeQuery(),
local!selectedEmployee,
local!hourlyRates:rule!hourlyRates(employeeName: local!selectedEmployee),
{
a!dropDownField(
label:"Select Employee",
choiceValues:index(local!employeeTable,"recordType!field",null),
choiceLabels:index(local!employeeTable,"recordType!field",null),
value:local!selectedEmployee,
saveinto:local!selectedEmployee,
placeHolder:"Select"
),

a!sectionLayout(
contents:{
display your data by indexing the field from local!hourlyRates
}
)
}

)

Make sure you have a null check for the filter/parameter.

I'm not sure if this is what your question was about. Please ignore if not.

Hope this will help.

M
michals0005Author
January 9, 2023

Hi Konduru.
Thanks for the reply, but that for me is not a valid solution. First of all I'm trying to display values and not get inputs. Both ways (for read/display and input) it does work for a small amount of record, and there it is valid. Cause let's say for 5 jobs shown, you are executing 1 query to retrieve these jobs, and then 5 other queries to retrieve dropdown content for each of the job entries. But imagine you have 500 jobs to display on a graph, or to build a calculation? Or a thousand of them. That would mean 501 queries or 1001. And that, even if it does the job, I would say would be not optimal - both in terms of operations needed and in terms of time needed to execute all of them.

So thanks again, but I'm still open for suggestions and answers how to tackle that.

H
harshitham3563
January 9, 2023

Hi [mention:902abe629d95444db2c5993d445523d8:e9ed411860ed4f2ba0265705b8793d05], I Think it will query only once and it will give us the desired output. Since you will be selecting the value from the dropdown your query will run only once and will give you the data. You can use this data to display the values. 

E
eunnelarciee103779
January 9, 2023

If it just for display, one possible solution is to use your SQL query to create a view, and make new record type and make the CDT of your view as source of data. or maybe just use queryEntity if it's possible for your use case.

M
michals0005Author
January 23, 2023

Hi Eunnel.
Thanks for the reply. All your points are valid and would work, but my question is just touching the surface of the problem, and with the full problem behind it, it's impossible to achieve.

In fact what we have is three tables:
A - table listing jobs
B - table listing job actions, each job action can have a different executor, all with have some execution timestamp with it
C - table listing executor wages with a wage value and time where given wage started to be effective

Originally, we are using queryEntity. But this is somehow limitting for us - we want to display a requested number of jobs with all their job actions and cost of them. And you can't put a limit on the jobs and at the same time retrieve a various number of job actions with them. ...or at least that's how I see this. If you create an entity of joined A + B + C, you are putting a row count limit on result of such cartesian multiplication. Hence limiting to ten, you may get two jobs (one with 4 actions, the other with 6 actions) and that's it.

Hence queryRecordType gives a huge benefit of being able to limit on the jobs, and then add related record type data from job actions. So in the end you can say 'give me 10 jobs, with their job actions, regardless how many job actions they have'.

local!testData: a!queryRecordType(
 recordType: 'recordType!A',
 fields: {
   recordType!A.fields.someField,
   recordType!B.fields.someOtherField,
 },
 relatedRecordData: {
   a!relatedRecordData(
     relationship: 'recordType!A.relationships.B',
     sort: {
       a!sortInfo(
         field: 'recordType!B.someOtherField',
         ascending: true
       )
     }
   )
 },
 pagingInfo: a!pagingInfo(
   startIndex: 1,
   batchSize: 10,
   sort: {
     a!sortInfo(
       field: 'recordType!A.fields.someTimestampField',
       ascending: true
     )
   }
 )
 ).data,

And here, correct me if I'm wrong, but records from a CDT, so records without sync enabled, will not allow you do add relationships, right? hence no possibility to add related record data (relatedRecordData inside queryRecordType).

...or at least that's how I'm seeing it. If I'm wrong at any point, feel free to point it out to me.
Regards
Michael

Terms & ConditionsAccessibility statement