GetSummary from two related tables

[Toni]

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?

[Lee Smith]

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

[Toni]

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 October 12, 2017 by Toni
deleted file with password

[Lee Smith]

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

[Toni]

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

[Lee Smith]

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

[Toni]

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