It shouldn't matter (to the app) if the data is coming from an API or a database query resource, it's an array of data that the transformer converts into date/product/order
you can see the data in the query, as I mentioned above it's being generated by the Retool dummy API endpoint so it's not "genuine" data
OK. This is all pretty new to me, so I am trying my best to decipher what is happening here.
As you stated, I do need to make some adjustments to accommodate my data. Instead of SKU and a number for each product, I actually have the name of the product (i.e. Monolith 1640, Goliath 1641, etc.). Then there is a serial number for each product that is different for each product (so the same serial number does not repeat).
I started by creating a SQL query to access my production data.
SELECT *
FROM production_schedule
Then I changed the transformer results from what you had:
return data.map(x => {return {
...x,
product_id: 'SKU_'+x.product_id,
build_date: moment(x.build_date, 'MMM D, YYYY h:mm A').format('DD-MM-YYYY'),
project_date: moment(x.project_date, 'MMM D, YYYY h:mm A').format('DD-MM-YYYY')
} })
to this:
return data.map(x => {return {
...x,
model: x.model,
build_date: moment(x.build_date, 'MMM D, YYYY h:mm A').format('DD-MM-YYYY'),
proj_date: moment(x.proj_date, 'MMM D, YYYY h:mm A').format('DD-MM-YYYY')
} })
with model being the product name.
Then in the formatOrders transformer, I removed the SKU from this line:
const product_list = Array(20).fill().map((x,i) => 'SKU_'+(i+1));
so I ended up with:
const product_list = Array(20).fill().map((x,i) => (i+1));
And this is where I started to get quite confused and it all started to fall apart on me.
I think a key part you might have missed is this comment 
// some fake product SKUs, you'd get this from your DB
I don't have a list of products, so I made up a dummy list, but you would need to get this from your data and not what is in the example.
The logic I used is:
- Query to get a list of all the products and serial numbers from the database (I didn't have this list so I used a made-up list using array fill)
- Query to get a list of all the orders with their dates, colours, and serial number from the database (I didn't have this list so I used the API generator to make up some data)
- For every day between "today" and "next week" create an entry for every product
- For every day, for every product create a record of all orders for that product on that day, and create an empty record if there are no orders
The end result is an array of days with a list of all products and all orders for those products.
If you preview the transformer and check the data, you should see the structure that the listview is then using, and it will help your understanding of what the output of the transformer looks like and where you need to input your own data.
1 Like
@dcartlidge, I tried all weekend to get this to work without any luck. Apparently it is just too far over my head. I do appreciate your efforts though and willingness to help. Thank you.
Noooo, I don't believe that for a second - you can definitely do this.
Show us how far you got and where you think you're stuck
1 Like
Not being well versed in javascript, I'm having some difficulty with the transformer you created. You said to test data you were using with my data and I guess I'm not understanding exactly where and how to do that.
No problem, it can be a tough learning curve. Are you more familiar with SQL?
I think you could start by getting a unique list of all the products.
So, in one of your screenshots it was listed as the "model" column in the database. See if you can get that data into a query to begin.
You'll eventually want to use the data from this query in the transformer code instead of product_list
Yes, I far more comfortable with SQL.
I attempted to do what I think you are saying. I changed a line in the transformer to:
// some fake product SKUs, you'd get this from your DB
const product_list = {{ query139.data }};
Is this correct? It didn't seem to change any of the output.
Looks fine. That's expected not to have updated anything else yet, it wouldn't be likely that your data is in the same format as my test data was.
Can you share what structure the data from query139 is in? A screenshot should be fine.
Next you need a query to get all the "orders" or "jobs" (not sure what you call them) and which products they relate to and which day they are for.
Again, if you can share what that data looks like it'll help to tie these together.
Something like this should be in the output response area of the query editor
The database for query139 is just one field, the list of pool models.
The production database just contains build_date, model, serial_number, color, add_ons and proj_date.
Does that help or do you need actual screenshots?
By the way, when I run the query to pull in all the orders, I don't get any rows returned. I have this in the transform results of the query.
return data.map(x => {return {
...x,
serial_no: x.serial_no,
build_date: moment(x.build_date, 'MMM D, YYYY h:mm A').format('DD-MM-YYYY'),
proj_date: moment(x.proj_date, 'MMM D, YYYY h:mm A').format('DD-MM-YYYY')
} })
Do the serial_numbers identify the pool models? Are they a unique number, like a SKU for each model?
You should select that in query139 too, in that case.
What we're trying to do is get 2 sets of data
1 - the list of all pool model name and their unique ID (serial or SKU or similar)
2 - the list of all scheduled jobs for the next week
When we have those 2 queries running and we know the names of the fields that they are selecting we can update the Transformer to use these queries. If you can get those 2 queries and share a sample record from each of them I can point you in the right direction on the transformer changes.
The serial number is unique to each order, but not each pool model. Each serial number will never be used more than once.
Here is the query I was using to pull all the pool model names. The database just has two columns, id and model_name.
SELECT model_name
FROM production_models
Here is a screenshot of what that database.
Then I use this query to pull back orders.
SELECT *
FROM production_schedule
Here is a screenshot of that database. I was originally thinking I would need to have the database set up this way and then when I pulled the data, it would already be formatted the way I needed, but I'm guessing now that is not correct.
Thanks again for your help.
Great work.
So the list of models has a slightly different name to the ones in the schedule data - is that right? There's a code in brackets for the models but not the schedules.
I'm wondering how you'd be able to join a product to a schedule if it's not by an ID (or that code in brackets).
The reason I'm asking that is that the pool name could be an option to join the data, but the names don't match up.
I could make them match. That would not be a big deal. I've sort of been all over the place with this, trying different things, and I tend to get a bit disorganized like that.
If they match and we can join them then that's ideal.
Your queries should return a list of all products and a list of all scheduled jobs.
Neither query should need to do any special transformation on the results at this point, I'd suggest all you'll want is return formatDataAsArray(data)
This will give you 2 arrays of data.
What the transformer will do is loop through the first array of products and find any scheduled jobs in the second array that match that product.
It's also going to do that loop for every day you want to display on screen - I used a week in the demo.
So you should consider how you want to filter your schedule data query - do you want to return all jobs or just the ones for the next week or some user-defined date range. Putting a clause into your query now might be useful.
OK. In the getOrders query under transform results I have the following code which you had provided.
return data.map(x => {return {
...x,
serial_no: x.serial_no,
build_date: moment(x.build_date, 'MMM D, YYYY h:mm A').format('DD-MM-YYYY'),
proj_date: moment(x.proj_date, 'MMM D, YYYY h:mm A').format('DD-MM-YYYY')
} })
Do I need to change that to
return formatDataAsArray(data)
yes, that's correct. Those transformer functions in the demo would only work with the demo data, not your data
OK, so I have the two arrays now. getOrders and poolModels. Do I plug those into the transformer like such:
// combine that with a list of all product codes
// create an array of orders by product by date
// some fake product SKUs, you'd get this from your DB
const product_list = {{ poolModels.data }};
// the data of all the orders
const order_list = {{ getOrders.data }};
// the start date for reporting, probably a parameter in your DB query or API calls
let reporting_date = moment('17-02-2025', 'DD-MM-YYYY');
// the range of the report, 1 week in this case
const reporting_range_days = 7;
// for each day of the report loop through every product and return a row for any/all orders for that product
// it will be a sparse array
// the array of orders we'll use in the listview
let order_data = {{ getOrders.data }};
for(let i=0; i<reporting_range_days; i++){
// find all orders for day i for every product
let today = reporting_date.format('DD-MM-YYYY');
// console.log('looking for orders on ',today);
let o = order_list.filter(x => x.build_date == today);
// console.log('orders for today ',o.length);
// temporary object of this days orders
let d = {
orders: [],
date: today
};
product_list.forEach(p => {
// console.log('looking for orders today with SKU ', p);
let po = o.filter(x => x.product_id === p);
// console.log('found ' , po.length, ' orders for ',p)
// merge an empty order object with whatever orders we found so this makes a line per product, not per order
d.orders.push(_.merge({
product_id: p,
project_date: null,
addons: null,
color: null
}, po[0]));
});
// move to the next day
reporting_date.add(1, 'days');
// console.log(d);
// add the data to the output array
order_data.push(d);
}
// console.log(order_data);
return order_data;
And the pool models now match in both databases.
Great news!
So the transformer will try to build a new list of all your data in date/product order and this is what is output from the transformer and used by the listview component. I called it order_data in the demo and it should start as an empty array, ie
let order_data = [];
What you now need to do is change the transformer so that it connects the product with the order AND you need to change the transformer to find orders for the right day which means you may need to format the build date so that the date formats match.
Here's a version of the transformer with more comments and tells you where you need to make changes.
It also adds some console log messages, which should help you see what's happening as the code runs.
You can see the console messages in the debug panel in retool, or by pressing F12 to view it in your browser console (sometimes the retool debug console can't display all the log messages)
// combine that with a list of all product codes
// create an array of orders by product by date
// a unique list of products from your database:
const product_list = {{ poolModels.data }};
console.log('The product list is ', product_list);
// a list of all the jobs from your database:
const order_list = {{ getOrders.data }};
console.log('The job list is ', order_list);
// the start date for reporting,
// using the moment library to do this for us makes it easier too
// you will want to change the date used below to match your orders data
// this is the first date that the listview will display
let reporting_date = moment('17-02-2025', 'DD-MM-YYYY');
// the range of the report, 1 week in this case
const reporting_range_days = 7;
// an empty array that we'll populate in the loops that follow
// this array is then returned by this transformer in the last line of the code so that the listview can display it
let order_data = [];
// i is the counter of how many days of data we've processed, we start at 0 and stop when we hit reporting_range_days
for(let i=0; i<reporting_range_days; i++){
// the date we want to find orders for, as a formatted string, using moment for ease
// we will use this same date format for consistency in comparisions
let today = reporting_date.format('DD-MM-YYYY');
console.log('Finding orders for day ',i, ' which is the date ', today);
// create an empty object to store "todays" orders in. we have 2 properties, the orders list and we have the date
// we may find orders, we may not, but every day needs to be checked and it may have an empty order list, but that's ok
let d = {
orders: [],
date: today
};
// filter the list of orders where the build_date matches our "today" string
// the important part here is:
// x.build_date == today
// x.build_date is the date of the job in your order_list data
// this is compared to the "today" date string
// the formats must match for it to know it's a job for that date
// you may need to use moment to format x.build_date so it can do the comparison, using the same date format string we've used earlier
let o = order_list.filter(x => x.build_date === today);
// how many orders have we found? 0 or more?
console.log(o.length, ' orders matching this date', o);
// we have a list of all "todays" orders
// now produce a list of every product and any orders on this day for those products
// ie we're using the filtered order list now
product_list.forEach(p => {
// This is where we join products and orders
// if your product list is a list of names you will need to change the next line where it is using x.product_id and p as the properties it's comparing
// if your product list is a name you may need to compare that with x.product_name, for example
let po = o.filter(x => x.product_id === p);
// po is an array (ie list) of orders, length tell us how many are in the list:
console.log('Found ', po.length, ' orders for ',today,' for product ', p);
// merge an empty order object with whatever orders we found so this makes a line per product, not per order
// this part doesn't need to change, it's creating an entry for "today" for each product but it does assume you only have 1 order per product per day
d.orders.push(_.merge({
product_id: p,
project_date: null,
addons: null,
color: null
}, po[0]));
});
// we've got all the orders for all the products for "today" so lets move on
// move to the next day using moment, again, to add 1 day to reporting_date
reporting_date.add(1, 'days');
console.log('The list of order for each product for todays date ', d);
// add this data to the output array which the listview will use
order_data.push(d);
}
console.log('The data for listview to display ');
console.log(order_data);
return order_data;
1 Like
The first time I ran the transformer after making the necessary changes, it seemed to run and I got an array, although the array was missing the build_date as well as proj_date, color and add-ons. All I got was the pool model name. I tried to make changes, and it stopped running. I set it back to the way it was when I got the array, and now I get nothing.
I checked the console. It says Error: missing: after property id in formatOrders, line 43.
Since I don't have a product_id for the products, I attempted to change this line: let po = o.filter(x => x.product_id === p); to this let po = o.filter(x => x.model === p); in order to use the product name, which is 'model' in the database. That is when the transformer starting returning no results. I changed it back and I still get no results.