Jump to content
Toni

GetSummary from two related tables

Recommended Posts

Hello guys

I am really struggling to get this job done which seems pretty easy.

I have two tables, let's call them Table A and Table B. On table A I have records of the product flow into the production chain (product goes from phase 1 to phase 2 and so on). On table B I have records which tracks the movements through export from Table A.

So table A will give me the situation on every department of the chain production, while Table B will give me totals based on day or hours or month etc.

I have created a third table, Table C, on which I want to get a day by day situation from the above tables. On table C I would want to get the summary from Table A and Table B. I have created a field on each table based on date and department which I use as relation from C to A and from C to B.

While I can successfully get the summary from table B I can not understand how I can not get from A. The field from A gets the total of B instead of its own.

Table A                                             Table B                               Table C

field1=department                          field1=department           field1=date

field2=product unit code                field2=product unit          field2=department

field3=quantity product unit          field3=quantity                 field3=getsummary tot department on A

field4=date                                       field4=date                        field4=getsummary tot department on B

Table A and B may look the same, but in A the product unit exists only once changing its department every time as it moves forward showing where it is at that moment. On B, the product unit will have as many instances as many times it has moved, keeping track of its movements on every stage.

Any idea what I might be doing wrong?

Share this post


Link to post
Share on other sites

Hi Toni,

Please don't use Generic names. for your tables, field names, etc. Usomg your actual names for these things will helps us understand your schema and our replies will make more sense to you.

Consider attaching a copy of your file, or a mockup. Learn how you can do this by reading this topic by Ocean West, Anatomy of a Good Topic.

Lee

Share this post


Link to post
Share on other sites

Thank you Lee, so here I go with actual field names on the database:

The database has 7 tables, but those at our interest are

  • ComNuovo
  • Inventari
  • Raport_Ditor

On ComNuovo table there is a summary field called Tot.Capi from where I want to get the summary when ordered by field=Rep.

On Inventari table there is a summary field called TotaleCapi from where I want to get the summary when ordered by field=Rep.

Both summary functions will be calculated on table Raport_Ditor where I have two fields Dalje_ComNuovo_Copy and Dalje_Inventar_Copy.

On the Raport_Ditor I was planning to create the records by script, but they can be created manually by filling only Rep. field and Date field, the rest is calculated. The field that is used to relate the tables is Date_Rep.

I am attaching the file.

 

Edited by Toni
deleted file with password

Share this post


Link to post
Share on other sites

Please provide the User Id and Password for your file,   Admin and admin didn't work. 

Share this post


Link to post
Share on other sites

Yes, sorry about that, the common mistake. Had in mind to change it but forgot.

I am attaching the file with user: admin and no password.

ComNuovo Clone Copy.zip

Share this post


Link to post
Share on other sites

Hello Toni,

I don't speak the language your file was written in? What is the language?  Perhaps someone who speaks your language will jump in.

I really struggled opening your file, was it setup for a server? Your startup script was looking for other related files. The layout I ended up on would not let me into Manage Database. I had to open it using Script Debugger in order to bypass your script. After it was open, I had to cancel it's search for your EDS files.

My first reply to this thread contained a link to Anatomy of a Good Topic, it explains further about how can prepare a file for posting it to the FM Forums,

Lee

Share this post


Link to post
Share on other sites

The language is italian and albanian, both used (don't ask me why :) ).

The menu is just a customized one, one can activate the standard with no problem. The files missing in relation are of no importance, can be ignored. The data is there to help with summaries, are not of great sensitivity, it's just a very small portion of the whole.

I see what I can do to make an imitation of the database without the hassle this one carries.

Thank you Lee

Share this post


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 cbombeck
      Hello Everyone 
      Very new to FileMaker here. 
      I have an Event Table with fields EventID, EventDate, and EventDuration, AND an Attendees Table with fields EventID, PersonID, and SignupCreatedTimeStamp. I would like to add a Field to the Attendees table that that will tell me how many hours of activities this person is already signed up for in the 30 days before/after that event. I can do this using the Find Records method, but I think this could be done in a Calculation Field using GetSummary. 
      The point of this all is to prevent people from signing up for more than 20 hours of activities in a month. After I get this calculation worked out, other checks will be put in place when creating new records to make sure people are in compliance. 
      To make this a little more flexible I added the following fields to the Events table 
      EventWindowStartDate = EventDate - 30 EventWindowEndDate = EventDate + 30 Guess what I'm not sure on is the syntax for GetSummary .... I've tried putting the following in a calculation field with no luck
      GetSummary(EventDuration, if(EventWindowStartDate...EventWindowEndDate, EventDuration, 0)) Am I going about this the right way? What is wrong with my calculation above? 
      Any thoughts or pointers would be much appreciated. Thank you very much in advance!! 
    • By ScottR
      I've got to believe I'm just having a brief episode of "stupid" and I'm just over thinking this...
       
      I'm essentially trying to create a portal of summaries of items that appear in another portal. Here's the plan...
       
      I have a table with individual expense items. I then I have table used for invoicing the expensed items. The data is sorted by date and customer and category creating an invoice with the total amounts. There are several items on each invoice of the same category (i.e. meals & entertainment, or car rental or hotel).
       
      A simplified list of expenses on the invoice might look like this..
       
      04/01/14   Hotel   $250
      04/20/14   Car      $63
      04/02/14   Meals   $50
      04/03/14   Hotel   $200
      04/12/14   Meals   $50
      04/06/14   Hotel   $175
      04/16/14   Meals   $48
       
       
      I'm trying to create a portal that lists the summaries of the categories.  Something like this...
       
      Hotels  $625
      Meals  $148
      Car      $63
       
      Since the categories are dynamic, and I'm filtering by invoice number I can't set up summary fields for each category. I've created a self-join relationship and used the GetNthRecord trick to grab the first occurrence of each category from the main portal but now trying to grab the totals of each category is presenting a problem. If I simply do a GetSummary of each category it only lists the amount of the first record (due to the GetNthRecord used in the portal filter).
       
      I'm looking forward to learning the errors of my ways! Thanks.
    • By Himself
      I have a form which uses a drop down list to store an ID and an edit box overlay to show the related value chosen. (still baffled that FM hasn't just made that a native option)

      The problem I am noticing is that the Edit box only refreshes when one clicks on a blank area on the form. I have drop down lists in other forms that refresh their edit boxes as soon as a value is selected. The difference being that the ones that refresh immediately, the drop down list field is from the table the form is based on, but if the drop down list field is based on a related table it doesn't refresh immediately.

      How do I get it to refresh without changing the forms base table reference or clicking on a blank area of the form?

      I have just let this behavior go but now the users are getting annoyed by it. I am on FM 11 but I am not sure all the users are.

      Thanks for any help or even just let me know if this is something that just is the way it is.
×

Important Information

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