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