Jump to content

Incorrect subsummary list view with related totals


Recommended Posts

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.

screen1.png.c6aa3a518c8de56b59e3f9ac64a4930a.png

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:

image.thumb.png.fb47a45f767501b87aaec1ac65356a01.png

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)
    • image.png.bd13069562afff66d72feb346ef0d169.png
  • 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
    • image.png.c764c7dc4f442393df507c62ce0da937.png

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 by Solvax
clarification
Link to post
Share on other sites
  • Solvax changed the title to Incorrect subsummary list view with related totals

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 by comment
  • Thanks 1
Link to post
Share on other sites
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:

image.png.8058d85ed160c15326e72bd6d3ba377b.png

Example where Voorraad_uit (=outgoing deliveries) is the layout table:image.png.556e56ab6369df51495ad196f511f4fa.png

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!

 

Link to post
Share on other sites

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".

 

  • Thanks 1
Link to post
Share on other sites

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.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By Thomas Sanguinetti
      Good day all,
      Issue -
      I have created the file Radio.fmp12, stored in the same networked location as my other filemaker files. (See attached 'Filemaker Files.png').
      I am however unable to open this file using a script as have previous done with my other files.
      When I open the Hosts window, all my other programs are displayed except for Radios.fmp12. (See attached 'Hosts.png')
      I attempted to open the file directly via its network path and filemaker alerted me that the file could not be found (See attached 'filepath.png')
      Please forgive me if i am missing something but i can open the file directly from the directory so i am baffled as to why the file cannot be found.
      Any help would be appreciated as I am somewhat of a beginner with Filemaker.
       
      Thank you in advance
      Tom
       



    • By ggt667
      FM18P: What is best practice when it comes to importing data using JSON / ION? I usually use XSLT and FMPXMLRESULT for import, is there a similar way to do these imports of records into FileMaker?

      FM19S consideration: I guess the simplest way is to write something in ECMA or Swift that outputs the portable array as FMPXMLRESULT? As pr example
      curl -kL https://api.domain.tld > /opt/local/usecaseION2FMPXMLRESULT > /opt/FileMaker\ Server/Data/Documents/usecase.$(date).fmpxmlresult.xml But then again FileMaker 19 Server does not allow for XML imports?
    • By ggt667
      I have been running on 2 copies of the same license in my house, in the mean time I bought a 5 user VLA, how can I change the license in FileMaker 18?, when the trial expires I can simply click Browse and click on the cert file that came with the VLA.
    • By Clayton King
      First, I appreciate any help I can get as this project has been evolving over two years and, as these kinds of projects to, has become more involved and complicated over that time. I've posted related questions, but exploring other parts of my project. As I'm not a programmer, but a sort of user nerd, my inquisitive mind wants to learn, but sometimes simple concepts elude me. Feedback, both on the specific goal at hand as well as the project overall is always appreciated.
      Performers is one piece of a much larger project, but I'm trying to enhance the project and working on a piece at a time. 
      BACKGROUND
      I'm sharing the entire project layout to provide some insight into how the parts fit together. The application is a booking management tool for performers, consisting is several tables as shown:

      Again my focus right now is the Performers table and how to use it in relation to the Gigs (and Gigs~Songs_Join) tables.
      There is a Gigs layout which is the hub for performances by one or more Performers. On that layout is summary information about a gig and a portal which shows which songs were performed and in what order (Set & Slot fields). The portal also includes a Performer Name. The assumption at this point is that up to 4 performers will do a given gig. This might change in the future, and could impact the below goals.
      Gigs~Songs_JOIN is a connecting table for Gigs, Performers, Songbook, Venues, Musicians, etc. Performers is a table of singers and includes fields for a serialized SingerID, name, area of speciality (Tenor, Baritone, etc.), etc., and additional fields for combinations of the same performers including: 
      Multi: checkbox-indicates this record is more than one performer
      PerformerName: calculated based on Multi: if NOT Multi, show performers Name, if Multi, show MultiName (below)
      Type: value list-is this a duet, trio, quarter, all, etc.
      MultiName: the description of this record (i.e., Duet-Clayton/Vicky)
      Other summary fields include:
      Total Gigs: count of gigs in which this person has performed
      Total Songs: count of songs this person has performed
      GigID: match field to Gigs~Songs_Join

      GOAL
      I have two goals, one related to the other. First, if the Performer record is checked Multi, I want to "attach" or "associate" individual singer(s) to the record (i.e., show what people are "included" in the MultiName). For example, MultiName=Duet-Clayton/Vicky, I want to show that Clayton and Vicky (each in separate records) are part of Duet-Clayton/Vicky.
      Second, ultimately on the Gigs layout, I want to show which Performer sang which song (currently works in the portal), but more importantly, show variable summary information (goal layout below) which changes for each Gig as to each Performer, how many songs they sang and the total amount of time they sang. The catch is, in the summary I don't want to show the Multi records, but instead increment individual singers. So, in the summary info with related portal records in the example below, rather than showing Clayton sang one song, Vicky sang one song and Duet:Clayton/Vicky sang one song, I want to show Clayton sang 2 songs and Vicky sang 2 songs. In summary info, Duet:Clayton/Vicky wouldn't appear. In other words, the summary would show how much time each person was singing.

       In the summary block of the Gig's layout, each of the above sets is a one-row portal filtered by set. I want to have the SINGER1,2,3,4 replaced with the actual Performer's name, and each band/portal show summary info for the appropriate singer. 
      Here is my relationship graph:

       
    • By ggt667
      For FileMaker hosted solution does anyone use $$VARIABLE for external data source? I'm able to make $$VARIABLE="file:filename" work, however $$VARIABLE="fmnet:/host/filename" does not.
      In my case this would be $$FM16S00CONTACT="fm16s00.domain.tld/contact" and $$FM18S00CONTACT="fm18s00.domain.tld/contact"
      This now works, however it's unclear to me what triggered this working. I have a startup script that sets the variables. I have configure the External Data Source with $$FM16S00CONTACT, and this works, however adding another data source does not work for now, I tried quitting FileMaker and relaunch.
  • Who Viewed the Topic

    5 members have viewed this topic:
    arni  JohnDing  Steve Martino  LAIRY  Richard Carlton 
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.