Looking for some possible ideas

[tomm]

I have been trying to find a way to clean up our shipping schedule. We have been using Google Sheets since the company started and I think we have outgrown it. Right now the data is just displayed in a spreadsheet that scrolls quite a bit left to right. It's pretty much impossible to get the whole spreadsheet to show up, even on an ultra wide. Here is the headers from my table in Retool right now:

Does anyone have any thoughts on any other way to display this data. This is something I have been struggling with for weeks. Any suggestions would be appreciated.

[MiguelOrtiz]

Hey @tomm,

I think it really depends on what columns need to be immediately visible, which ones are secondary and which ones are rarely used.

I think you may find this blog post from BoldTech as a source of inspiration. I use all of the things mentioned in the article, i.e captions, tooltips, expandable rows, etc.

Do let me know if you need help with any of the above!

[tomm]

@MiguelOrtiz, sorry for taking up so much of your time, but I appreciate the help. I did come up with a new way to display the data that I am trying to implement using list views. One of the issues I am having with that is trying to get the data grouped together correctly. Let me see if I can explain this.

There are times that we have numerous loads going with the same driver because they are either all going to the same location or the delivery addresses are very close together. In the list view, I would love to be able to put those shipments in the same view, instead of each of them being in a different view.

Here is what I currently have:

Those three shipments in the screen shot, along with five more, are all going to the same location with the same driver. Is there any way to create a SQL statement that would group those together in the same container? I have tried assigning a load number to each record, so all these records would be load 1, and I tried the following query:

SELECT *
FROM shipping
WHERE ship_date = {{moment(new Date())}}
GROUP BY id, dealer, load_num
ORDER BY ship_date, load_num, dealer

Unfortunately, that did not seem to change anything.

Thanks in advance for any additional help you could provide.

[tomm]

Also, when using the list view, is there a way to control the number of containers that are shown?

[MiguelOrtiz]

Hey @tomm,

not a problem, happy to help (besides I've been there too not long ago and this forum helped a lot!).

So, the way I would approach it is to create nested arrays within one "record", and group each record by a common denominator, which in this case it seems to be the carrier.

I usually do this with my SQL query like this:

SELECT
field1,
field2,
field3,
field4,
json_agg(
    json_build_object(
      'field5', field5,
      'field6', field6,
      'field7', field7,
      'field8', field8
    )
  ) AS shipments
FROM shipping
WHERE ship_date = {{moment(new Date())}}
GROUP BY field1, field2, field3, field4
ORDER BY field1, field2

This should give you one record for each Carrier/Address with nested arrays for each shipment that fall under the same group.

Another way is to use a "Query JSON with SQL" resource where you can basically do the same, i.e. group records and nest records within them by building arrays. You would have your main query, and then use the second query json with sql resource to structure the data from the main query.

This is the structure I've used for this kind of resource (apologies for not removing context from my own app):

SELECT
  product_name,
  id,
  product_type,
  business_unit_id,
  default_monthly_cost,
  currency,
  ARRAY(
      @{
          lead_id: (lead_id),
          lead_value: (lead_value),
          lead_currency: (lead_currency),
          active: (active),
          ongoing_programs_count: (ongoing_programs_count),
          opportunity_name: (opportunity_name),
          stage_id: (stage_id),
          stage_name: (stage_name),
          funnel_name: (funnel_name)
       }
  ) AS details
FROM {{getProductStats.data}}
GROUP BY
  product_name,
  business_unit_id,
  id,
  product_type,
  default_monthly_cost,
  currency
ORDER BY
  name

With regards to the UI, I think you will need a nested listview within your parent listView. With the same logic, the parent listView holds the main records with data source being query1.data, and the nested listView is for yoru records, with the datasource being item.shipments (as it is a nested listView you can use item to refer to the whole record).

Hope this helps as guidance. Keep throwing your questions if you get stuck!

[tomm]

Hey @MiguelOrtiz,

This is slightly off-topic, but I could really use some help.

I had a page set up that was displaying five table, each with data from five different database tables. I also has some queries that were being used to move data from one table to another and to delete records.

I had decided to put each table in a collapsible container and only have the queries that populate each table run when the container is opened. In order to make this work, I had to set the queries to run manually. The problem with manual is that when I attempted to move a record or delete a record, those no long worked.

To do the move record and delete records, I added row actions and when the icon is clicked in hovered row, it runs one the scripts based on whether the record is being moved, or deleted.

move_no_deposit_to_new_orders.trigger({
  onSuccess: delete_from_no_deposit.trigger({
    onSuccess: no_deposit_orders.trigger({
      onSuccess: new_orders_2024.trigger({
        onSuccess: utils.confetti.trigger()
      })  
    })
  })
});

delete_from_no_deposit.trigger({
  onSuccess: no_deposit_orders.trigger({
    onSuccess: utils.confetti.trigger()
  })
});

So, I thought it might be a problem with having the set run behavior to manual, so I starting changing them back to automatic. On doing it so, it started moving every single record from one table to another. The query just kept running over and over. I finally got it stopped by going back to manual. Then I set the delete query back to automatic and the same thing happened and it deleted all the data from the table. Now I'm afraid to try and change those back to automatic. Any suggestions?

[tomm]

OK. I have restored my data and set all queries to manual except for the ones that initially populate the tables. I have also moved the tables to a tabbed container rather than using the collapsible containers.

Unfortunately, the delete function on the first table does not seem to be working. Here is the query to delete a record.

Then I have the delete set up as a row action.

And the script that I am trying run is:

delete_from_no_deposit.trigger({
  onSuccess: no_deposit_orders.trigger({
    onSuccess: utils.confetti.trigger()
  })
});

But when I click on the delete icon that appears when hovering over a row, nothing happens.

[Tess]

Does it work if you change utils.confetti.trigger() to utils.confetti()?

Scripts can be tricky in that you won't see failure notifications like you do for queries. If it still doesn't work after changing the confetti part, it could be worth running it in a JS query to see if you get a more helpful error message.