Looking for a faster way to update a nested table in a Detail form

With the kind assistance of Marc_Dillon I worked out how to get a specific summary to appear on a PDF report but for my app to be fully functional I need to be able to get the information on a form before the PDF is generated. I’ve tried various approaches, and while I can get it to work, it is too slow to work with.

My tables are structured as follows:

Orders (Parent) > Daysheets (child) > Materials Used (Grandchild)

A Daysheet may have related Materials Used of the same product entered multiple times and the same Materials Used may also be linked to numerous Day Sheets.

The process I need to cater to, on a Windows PC, is as follows:

  • Display a List of orders that are ready for invoicing using a table view form.
  • When order is selected a Detail view form displays order details including a nested list form of related day sheets.
  • Actions buttons allow user to select the day sheets to be included. (The second Action column in the screen print below is not really relevant to this question)
  • As user selects each DaySheet from the list the action changes the X to a check and adds the DaySheet’s OrderId value to the Select field
  • A nested form refreshes to display all the materials Used by Selected, Related Daysheets.
  • Another nested form displays a total of the materials used by Material

I’m not sure that I have explained it well so I have included a screen print of the Display form including the 2 nested tables. It all works and the list of materials by DaySheet (step 5 -Related Selected Materials in the screen print below) is very fast, however step 6, Selected Materials in the screen print, is very slow, probably because the convoluted way I am creating the totals in the Materials table is creating the drag.

I would really appreciate any help or direction you can provide, as this is the last, major, component of the entire process to implement this app into the business.

For information sake, the code that works well on the email Report is as follows:

START: <<UNIQUE(SELECT(MaterialsUsed[Item Number],[Day Sheet Id].[OrderId]=[_THISROW-1]))>><<SUM(SELECT(MaterialsUsed[Qty],AND([Day Sheet Id].[OrderId]=[_THISROW-2],[Item Number] = [_THISROW-1].[Item Number])))>>

Not fully following (too early; brain not warmed up yet), but it kinda sounds like at least partially a job for an interactive dashboard?


Thanks, i thought about that and built the dashboard, but I am not sure how to then manage the second step, i.e. how to get the List of orders to open the Dashboard on the selected order. I assume I create an Action linked to the Order table, and get it to open another view within the app and set the Target to my dashboard. But I am not sure how to tell it to open to the record that was selected from the Orders list? Or are you suggesting I use the Orders List as one of the sections in my dashboard?

You’ve given me the ideas I was looking for. I will take a couple of days to see if I can work this out. Thanks

1 Like

Ok, I found a way to achieve what I need using a dashboard, as suggested, and using grouping to add my totals (group aggregate Sum) instead of trying to summarise by another table. Works well.

However I am having one issue that is perplexing me.

My Dashboard starts with a Ready To Invoice table view of an Orders Ready to Invoice slice, grouped by [Who For].
When the user clicks on an order, one view displays the order details and another view in the dashboard displays the related DaySheets. From there the user can select the desired Day Sheets and another view then displays the Selected materials on each of the Day Sheets, grouped by Material with a Group Aggregate. Works great.

However what I need is to run an action to Reset the Select field in my Daysheet table to blank when the user selects a different Order. To achieve that I changed the auto event action when the row is selected on the the Ready to Invoice form to an action that blanks out all the Selects. If I attach the Action to a variable and click on it, it does what it’s meant to. However if I select it as the action to run as the Row Selected event, it doesn’t work All that happens is the the selected row toggles between activated and deactivated. Is there a way To make it run my Action instead of toggling?

Please post screenshots of the action configurations.

I have shown below all the relevant screen prints.
This is how the Dashboard is set up

This is how the Ready To Invoice view is set up. The last Action (Compose Email) is not necessary, it’s just there to quickly see if the Action has run.

The Ready To Invoice Orders Slice on which it is based includes the following

The Reset Selects Action is set up as follows. For testing purposes I attached it to a column but it won’t work even if I set it to Do not display. If I press the icon, it works but it won’t work when I select a record.

The first Action in the above list is built as follows

The Action it calls Invoicing Set Select to Blank is built as follows:

1 Like

The screenshots you posted look good. Please also post a screenshot of the table view configuration showing the Row Selected setting.

1 Like

Hi Steve, sorry for the delay replying.

Not sure which one you mean, as I thought it was included. The dashboard top level is based on the Ready To Invoice Orders slice. Following are screen shots of that. Is that what you’re looking to see? I thought that, since I want this Action to fire when the user selects a new order, it should go here

To be honest, I have found a workaround for this so I don’t really need a solution but I remain curious, and my workaround is not as elegant as it would be if I could get this to work.