Incorrect subsummary list view with related totals

[Solvax]

Hi,

I'm trying to make a subsummary report with data from the current table and a related table.
I never get to display the correct totals for the related table, and would therefore appreciate your help.

First I'll try to explain the workflow:

I have two tables called 'Voorraad_uit' (= outgoing stock) and 'Voorraad_in' (= incoming stock) which are related by delivery number.
Data is first entered in 'Voorraad_in' (= incoming stock) and then related records are created in 'Voorraad_uit' when an incoming stock item is partly or completely shipped.  This second table contains mainly the outgoing amount and a unique outgoing delivery ID.

As a consequence, one record in table 'Voorraad_in' (= incoming stock) can have one ore more related records in 'Voorraad_uit' (=outgoing stock).

I want to make a subsummary report showing the total amount of incoming and outgoing units per product(name) by using the following layout structure:

I tried two scenarios which each give me a part of the desired result.

• when I base the layout on 'Voorraad_in' (=incoming stock):
  • I get the right totals for incoming deliveries
  • I get all the incoming deliveries listed (also those without outgoing delivery)
  • Body part 3 (see above) only shows one row also when there are more than one related outgoing transactions
  • I get the wrong totals for outgoing deliveries (only based on one record)
  • 
• when I base the layout on 'Voorraad_uit' (=outgoing stock):
  • I get the wrong totals for incoming deliveries
  • I don't get all the incoming deliveries listed (only those with outgoing delivery)
  • Body part 3 (see above) shows all the related outgoing transactions
  • I get the right totals for outgoing deliveries
  • 

Main goal is having the right totals for both incoming and outgoing deliveries..
My layout is sorted ascending by (in mentioned order) productname, incoming delivery number and outgoing delivery number.

The totals are calculated by using summary fields.

I hope I'm on the right track and that the desired result is possible.. your help is much appreciated!
Thanks in advance for your help.

Regards,
Solvax

Edited January 3, 2021 by Solvax
clarification

[comment]

Is the delivery number unique in the Voorraad_in table? Your screenshot suggests it's not, but it seems like it should be.

Also, you have conflicting statements about what the report should show: in your other screenshot you say that the body part should show the total amount of outgoing deliveries; later on you complain that: 

• Body part 3 (see above) only shows one row also when there are more than one related outgoing transactions

Do you want your report to show a row for every single outgoing amount?

Edited January 3, 2021 by comment

[Solvax]

20 hours ago, comment said:

Is the delivery number unique in the Voorraad_in table? Your screenshot suggests it's not, but it seems like it should be.

The delivery number (starting with L) is indeed unique in the Voorraad_in table, but it is possible that the delivered product is the same. In this instance I have just copied some product info to test, but the delivery number is unique. The outgoing transaction number (starting with T) is unique as well.

Also, you have conflicting statements about what the report should show: in your other screenshot you say that the body part should show the total amount of outgoing deliveries; later on you complain that: 

• Body part 3 (see above) only shows one row also when there are more than one related outgoing transactions

The goal is to show all the outgoing amounts (all outgoing deliveries also called transactions (T) for every incoming delivery (L)) and then make a total of them. I notice now that I didn't add the best screenshots to show the situation, see below attached a new example to show the respective screenshots

Do you want your report to show a row for every single outgoing amount? Yes if possible

Hi, I have added my reply in your answer.
Thanks again for your time.

Example where Voorraad_in (= incoming deliveries) is the layout table:

Example where Voorraad_uit (=outgoing deliveries) is the layout table:

So really, what I would like is to have the combination of both.. where you have all the outgoing deliveries shown per incoming delivery (screen 2) with the right totals for both incoming (screen 1) and outgoing (screen 2). And even more perfect would be to have all the deliveries shown (also those without transaction), but that would require the layout to be based on table incoming deliveries (Voorraad_in) I suppose.

If you have any ideas on how to solve this, would be welcomed, since I don't find the issue..

Thank you!

 

[comment]

I am still a bit confused regarding the format you want because you show grouping by product name, but I don't see a products table.

In any case, you seem to have put the finger on the real problem here: in order to show a row for each record in the child table, you must produce the report from the child table. But then parents with no children will not be shown. Filemaker native reporting is unable to satisfy both requirements. You need to do something else. I can think of three possible solutions:

1. Produce the report from the parent table and use a portal to show the child records. This is the easiest solution, however it has a flaw: portals do not print well across  page breaks. Still, with only 2 or 3 rows per portal, this might work for you.

2. Produce the report from the parent table and use a calculation field to produce the child record rows as text with line breaks. Again, you need to test this to see how it behaves around page breaks.

3. Produce the report from a reporting table. This is an advanced technique and I don't have time to expand on it now. But you would do well reading up on techniques known as "Fast Summaries" and "Virtual List".

 

[Solvax]

Thank you for your help on this. I was not aware of all these suggestions you have, so I learned a lot.

Will look into these solutions, thank you so much.