Is it possible to make two tables scroll horizontally in sync with one another?

[dcartlidge]

I checked the console. It says Error: missing: after property id in formatOrders, line 43.

It sounds like you've mistyped or made a syntax error then. Check your change against the original code.

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.

Sounds like the right idea! But what does your array of product data look like? Not what's in the database, not what the column is called, what is your array of products?
The console messages should tell you more - that should give you a step by step of what it's trying to do, what data it's trying to use.
It will have something like this message:
Found 1 orders for 23-02-2025 for product SKU_9
Which tells you the date it's looking for and the product it's trying to look for.
It's hard for me to know without seeing that, or the data, what the issue is.

[tomm]

I found the error and ran the transformer again and I got the array back. Here is what that looks like.

This what I am getting on the console:

Also, do I need to do something with this part of the transformer code in order for the values to all be null?

 d.orders.push(_.merge({
      model: p,
      serial_no: null,
      proj_date: null,
      add_ons: null,
      color: null
    }, po[0]));
  });

[dcartlidge]

Are you definitely using the code I pasted earlier today, that had a lot of console.log commands in it which should have shown in your debug console but there don't appear to be any.

As for the output array, it doesn't quite look right but it's close.
This is the structure that we're trying to achieve:

And the serial_no, project_date fields will need to come from your orders data - the names of the fields will need to be updated to what you have in your data. As before, though, I can't see your data so I don't know what you have named the fields.

[tomm]

The debug console showed no errors, but it also didn't show any of the messages you included in message.

I changed the product_id to model and the array ran, but I can't seem to get it to look like the one you posted above. Here is the transformer with all the changes I made.

// take all the orders for the date range (assuming your API or DB call gets data for a date range)
// 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('08-12-2024', 'MM-DD-YYYY');
// the range of the report, 1 week in this case
const reporting_range_days = 30;

// 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 = [];

for(let i=0; i<reporting_range_days; i++){
  // find all orders for day i for every product
  let today = reporting_date.format('MM-DD-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.model === 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({
      model: p,
      serial_no: {{ getOrders.data.serial_no }},
      proj_date: {{ getOrders.data.proj_date }},
      add_ons: {{ getOrders.data.add_ons }},
      color: {{ getOrders.data.color }}
    }, 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;

I'm not really sure what I am doing wrong at this point.

[dcartlidge]

Your code looks quite different to the one I posted here
This will explain why you're not seeing the console messages.

[tomm]

Sorry. Here is the correct code I am using.

// 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('08-12-2025', 'MM-DD-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('MM-DD-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.model === 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({
      model: p,
      serial_no: {{ getOrders.data.serial_no }},
      proj_date: {{ getOrders.data.proj_date }},
      add_ons: {{ getOrders.data.add_ons }},
      color: {{ getOrders.data.color }}
    }, 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;

Here is a screenshot of the console:

My array is still not coming out correctly. It looks the same as the last one I sent you.

I'm also not sure why it is only pulling Aug 18, 2024. There are no orders in the database for that date because it was a weekend.

I thought maybe I had the wrong date format. I tried MM-DD-YYYY and YYYY-MM-DD and neither of those made the array look any different.

[dcartlidge]

Don't worry about the final array yet. There are a number of steps you need to take before this is finished.

I'm also not sure why it is only pulling Aug 18, 2024.

How do you know that's all it's doing? You've given it a start date of 08-12-2025 and the last one in the console is checking against 08-18-2025, so I'd suggest that it has processed every day several days.
Scroll up the console and you should see the rest of the messages from each day it checked. If the debug console doesn't show it (because the Retool console can run out of space) then check the browser console by pressing F12.

Let's assume that date is being processed correctly, because I can't see evidence it's not, and look at the next steps:

Look at the message where it's trying to find orders for each model.
It's comparing a product_id with something in the console message that looks like this:
Found 0 orders for 08-18-2025 for product >{model_name: "Lil Bob"}

Notice those curly braces around the name? That's an object with a property called "model_name". If you're comparing an object to a string, such as the pool name in this case, then it won't match.

Check this section of the code and the comment, it may be what you need to change next

// 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.model === p);

Now there's one other area you need to consider and that's if you're getting the list of orders for each day. Again, it's a comparison question. Look for this section of the code:

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);

If your build_date value doesn't look the same as your today value then the console will be saying `0 orders matching this date' a lot.

Hope this helps point you in the right direction

[tomm]

I am having a very difficult time understanding this section of the code.

// 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.model === 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

Where it says let po = o.filter(x=>x.model === p);, if I mouse over x.model, I get this:

instead of just getting one model. Is that part of the problem?

[dcartlidge]

Nope, I don't think it is. The screenshot you're showing there is confirming to me that you have list of model names, this is what we were expecting to see and I think it's ok. The thing it's comparing it to (in your previous message) is not also a string so the comparison is failing to find a match.

I'll try to explain in different terms. The logic is:

I have a list of product names and a list of orders - for every product name in my list find me all the orders with this product name

product_list.forEach(p => {
aka "for every product name in my list"
p should be each of the pool names from your product_list query, eg "Aspen 1640 - A" and we'll run the next command for each of those names:
let po = o.filter(x => x.model === p);
aka "find me all the orders with this product name"
This will run the comparison for every order and only return ones where the order's model is exactly the same as p
This is like a where clause in SQL - select * from orders where model = p
Each of the orders is compared to the product name.
So if it were SQL we'd be looking to match a column against a property, like "model = 'p'"
This doesn't seem to match, though, so why might that be?

Well, in your previous message you showed the console message that said:
Found 0 orders for 08-18-2025 for product >{model_name: "Lil Bob"}
This means that p is not the name of the model it's a record that has a model name within it.
In SQL terms that's like comparing a column value with an entire row.
In JavaScript terms that's comparing a string with an object (you can think of it like a record or row)

eg select * from orders where model_name = {order_id:123,name:ABC,color:blue}
It just doesn't make sense. But that's what we've got here, you've got a comparison between a value and a record.

So instead of trying to change x.model, look at what p is and maybe change that so that values are equivalent.

In JavaScript you can access the columns of your record with the dot notation, so an object/record/row/whatever you want to call it such as this:

p = { model_name: 'Aspen 1640 - A' , color: 'blue', size: 'XL' }

has multiple properties and if you want to reference one of them you can use a dot, eg p.model_name would give you the string "Aspen 1640 - A"

The key is making sure you're comparing like for like.
if x.model is a string "Aspen 1640 - A" then p also needs to be a string. I don't think it is though, and the console confirms it.

[tomm]

Thanks @dcartlidge. Your explanation was very helpful. I think I am getting closer. It looks like the format of my array is correct now. This is what I get:

I'm still not getting any values for serial_no, proj_date, add_on or color, but I think I am getting closer.

[tomm]

@dcartlidge, when I changed add_ons: {{ getOrders.data.add_ons to add_ons: getOrders.data[0].add_ons, I got a value for add_ons, but it is the same value for every record. Is there a variable I should use in place of [0]?

[dcartlidge]

So, the [0] notation is used to get an item out of a list by its position.
ie 0 is the first item in an array, 1 is the second and so on
If you specify that each add_on for each order is the first add_on in your data then every order record will have the same value, which is what you're seeing.

You shouldn't need to specify add_ons: {{ getOrders.data.add_ons at all, those values should already be in your "get orders" query

I would guess, without seeing the data, that the order data either doesn't have the same fields as we expected or they're possibly empty.

Which part of the code is it you're changing? And what does the console say your order data looks like?

[tomm]

I did change code here:

model: p.model_name,
serial_no: {{ getOrders.data[0].serial_no }},
      proj_date: {{ getOrders.data[0].proj_date }},
      add_ons: {{ getOrders.data[0].add_ons }},
      color: {{ getOrders.data[0].color }}

I tried using this instead:

model: p.model_name,
      serial_no: o..serial_no,
      proj_date: o.proj_date,
      add_ons: o.add_ons,
      color: o.color

but then all the values came back as undefined. I also tried changing all the o's to order_list and that didn't work either.

I feel like I should be able to get this, but it's just not coming to me.

[dcartlidge]

hmm. It looks like you're trying to assign values from your order data, which makes sense.
I think maybe you shouldn't need to do it this way, though. The code should return your order data for every product name that it finds:

Was it this section of the code?:

// 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]));

Again, it might help if you can share what the order data looks like in your query or in the console.

[tomm]

Yes, I was changing that code.

My array currently looiks like this

My console looks like this

I'm not sure if this is what you were looking for or not. If not, please let me know and I'll try to get whatever you need.

[dcartlidge]

great, so if you click on the arrow on the log entry that says "the job list is..." then that will show you the data structure of what the code is calling "orders"
You'll want it to look something like this but it'll have different properties than this demo data, but roughly I'd expect it'll have similar things like colour and addon.

If it doesn't then you'll probably need to adjust the query to make sure it's returning the data you expect in the format you expect.
Change the code back to how it was before you made your last changes before you go again.

[tomm]

When you say to change the code back to what it was before, do you mean this:

d.orders.push(_.merge({
      odel: p.model_name,
      serial_no: o.serial_no,
      proj_date: o.proj_date,
      add_ons: o.add_ons,
      color: o.color,
    }, po[0]));

I am now not returning any records at all.

[tomm]

OK, using this code

d.orders.push(_.merge({
      model: p.model_name,
      project_date: o.proj_date,
      add_ons: o.add_ons,
      color: o.color
    }, po[0]));
  });

I am getting this array

[dcartlidge]

That looks fine, so what you need to change is the name of the properties and put that code you changed back to how it was.
ie

    d.orders.push(_.merge({
      product_id: p,
      project_date: null,
      addons: null,
      color: null
    }, po[0]));
  });

might need to be something that matches your jobs data like

d.orders.push(_.merge({
      model: p,
      proj_date: null,
      add_ons: null,
      color: null
    }, po[0]));
  });

Once the "data for listview to display" shows data then you can update the table in the listview display to use these column names.

[tomm]

I changed the code to this to match the fields in the database:

and now I get this in the console: