I am trying to build a table in a web app. My data comes from an API source and goes into a retool Database. This data is in multiple tables. The unique number would be a Job Number. One table, the Jobs table, has a list of all completed jobs. The second table has all line items. Every job has multiple line items.
What I am trying to do is make a table that list the jobs in the jobs table with some columns that have the totals added together from the line item table. My kicker is, I want to search the line item table and add up the "total" column when the job_number matches the job_number from the jobs table AND the "type" matches "Service".
I have tried all different types of transformers from conct to var but cannot get anything to work.
I want to be able to put this value into a column on the jobs visual table in the app for each job number.
Is this possible? This would go in the total book price column on the bottom table. The top one is a grouped table of the items database.
