Looking for a faster way to update a nested table ...

Griff · 03-21-2021 11:01 PM

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

Steve

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

Griff

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

Griff

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?

Steve

Please post screenshots of the action configurations.

Griff

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:

Steve

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

Griff

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.

Kirk_Masden

Was this ever resolved? I’m trying to figure out how to use this “Event Actions” feature (“Row Selected” → my action). What happens in my case is that the action is invoked but it does who the row that was selected. Is that the problem you had?

Griff

Hi Kirk,

What I needed was the ability to:

select an order from a list
2 immediately select the Day sheets linked to that order that I want to invoice,
display the totals so I could create an invoice in our accounting system,
create a PDF attachment of the selected, included Day Sheets to attach to the invoice
choose whether to close out the order or leave it open for further work but mark off Day Sheets that were selected and either included or excluded from the invoice.

My problem was that I needed to refresh the display progressively as I made my selections before syncing, otherwise it was simply too slow to be workable.

I ended up succeeding but it’s not especially robust or elegant because it has a few quirks that the user needs to deal with.

Not sure what you’re trying to achieve but I now have 6 forms on a dashboard. Following is a summary of what it does. I can give you as much information as you want if you tell me what part of the process interests you.

Form 1 displays a list of Orders, grouped by Customer.
When the user clicks on an Order on Form 1, Form 2 instantly refreshes

Form 2 displays the selected order details plus a few totals that are progressively updated as I interact with other forms on the dashboard such as total included and excluded labour hours, total included and excluded labour costs, total included and excluded material costs and total costs. I use some of these totals to enter into my Invoicing software and see how profitable it has been.

Form 2 also has an Action button that I press when I have finished my selections and entered an ivoice number (Form 6). This action creates a PDF with all the selected, included Day Sheets and saves it in a designated folder that I can then attach to the invoce. This is the only part of the process performed at the server while syncing and so is very slow. Therefore I need to save the invoices as later match the saved invoice with the saved PDF and email it to the customer.

Form 3 displays a list of Day Sheets associated with the order.
The list includes an Action attached to a field on each record on that form displayed as an icon. If the user clicks on that icon it flags that Day Sheet as one to be included in the related totals on Form 2, immediately updates related forms, 4 and 5, and makes a second icon visible.

The second Action (Icon) leaves the the record as selected on this form but marks it as being an excluded Day Sheet so it again refreshes the totals on the associated forms 3 and 4.

Form 4 displays a list of included materials used on the Day Sheets selected from Form 3 grouped and totalled by Material. I use the group total to enter the material name and quantity on the invoice

From 5 displays a list of excluded materials used on the Day Sheets selected from Form 3 grouped and totalled by Material. Excluded materials are not invoiced but their value is included in the total cost calculation.

Form 6 allows me to enter an invoice number and indicate if the Order is closed or not.
After using the data displayed on the various forms to enter the invoice on our accounting system, I enter the invoice number here and press the button on Form 2 to create the PDF attachment. The same action refreshes all the forms ready for the next Order/Invoice to be processed.

Let me know if any of the steps are of interest

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