have 4 Files of sales north west east south.
This is not ideal. It would be better to store all of this data in the same table of the same file with an added field that identifies the region for each record. You can use Import Records to pull of this data into such a single table.
Given that you have many more than four files for your lego sets, this is even more the case.
What you describe is really what manufacturing databases call a BOM, a bill of materials--a list of all items and their quantities needed to assemble/manufacture a single unit of product.
So you might consider this many to many relationship:
LegoSets::__pkLegoSetID = BOM::_fkLegoSetID
LegoParts::__pkLegoPartID = BOM::_fkLegoPartID
Note that the same part can be found and used in multiple lego sets. The existence of a specific part in a specific lego set is documented in the BOM table where each record links a particular LegoSet to a particular part. A portal to BOM on the LegoSets layout can list every part used in that set. A portal to BOM on the LegoParts layout could list every Set that uses that part. (When setting up a portal to the BOM on the LegoSets layout, you can include fields from LegoParts in the portal row. When setting up a portal to the BOM on the LegoParts layout, you can include fields from the LegoSets table.)
Thanks for your comments. I will do some testing with smaller files containing less records. So far I have mostly used only one table included dummy fields for "internal" relations. The reason for experimenting with individual tables was more of seeking a challenge since FM-design is more of a hobby for me keeping that 70year old brain fit. My other hobby is playing with Lego. Being a retired manager its a great joy now not focus anymore on next quarter's result..
It may take a while but I will be back - hopefully with some contribution for others to use.
So - finally I’ve found more than one option. You may need some flexibility regarding my use of the english names for menu items etc. since I translate that from German.
I imported all 4 Tables in one table „ALL SALES“ - adding an additional field to distinguish the regions south west north east.
The other field were product data and salesvolume incl. main categories like bread have the same article number
In the FM-management menu I duplicate the „ALL SALES“ and named it „ARTICLES“
Create a relationship between „ALL SALES“ and „ARTICLES“ based on article numbers
I created a field sum(ARTICLES::salesvolume)
If you put the fields from „ARTICLES“ in a portal and sort it by article number you see the individual items like
Products salesvolume total
12345 bread toast 500 800
12345 bread wheat 200 800
12345 bread sliced 100 800
The above is in the formula view i.e. you always see only the one related to the article shown in the current window.
In the tableview (the 3. viewing option) you see all items of the FM-file.
For practical purpose useable but what is not so nice … the summary of one particular article shows up next to all lines for that article.
So I decide to try out FM option of subtotals. I have attempted to use subtotals several times in the past already and gave up because I did not manage to force subtotals in an existing table. I read FM help more carefully and realized that you need to start with a new layout.
Details can be found here: http://www.filemaker.com/help/12/fmp/html/non_toc.46.14.html
That’s exactly what I wanted in the first place
As I wrote that all is to be used for my LEGO-hobby. My final plan is to add the parts needed for interesting constructions. Using a script it should be possible to change the number of parts needed to a negative value. If my subtotal table shows no negative sums I can build that construction. Otherwise I can easily see what's missing.
As a side effect I found 2 more ways to links tables in a multilevel way. The relationships are for example based on article numbers. The top-level uses the plain article number to build a relationship to the „inbetween-level“. In the direction to the base level the inbetween-level uses article number preceeded by the letter of that particular table:
Top-level inbetween inbetween lowest level A level B level C level D
1234 … 1234 A1234 A1234
If I understand it correctly the whole concept is called „ghost system“ or so
A similar construction is this one using a virtual table as inbetween
Finally I found in the same post a neat modification which also employs a script:
It is from a German guy but the FM coding should translate automatically in your local language.
3) + 4) are from this German forum-post:
You can open the above mentioned .fm7 in FM12 where they are converted without affecting the original.