Combine Like Records

[Reid]

How would I combine similar records? I'm showing a condensed version of a report and want all of the same categories to be combined into one sum total. See attached for better explanation.

Thanks!

[comment]

Use a summary field placed in a sub-summary part by category:

http://www.filemaker.com/11help/html/create_layout.9.15.html#1028102

[Reid]

Use a summary field placed in a sub-summary part by category:

http://www.filemaker.com/11help/html/create_layout.9.15.html#1028102

Do I have to have a summary field for each category?

[comment]

No. You only need one sub-summary part, with one summary field (Total of Quantity). Once you sort your records by category, Filemaker will repeat the same sub-summary part for each category, and show the correct sub-summary value in the summary field.

[Reid]

No. You only need one sub-summary part, with one summary field (Total of Quantity). Once you sort your records by category, Filemaker will repeat the same sub-summary part for each category, and show the correct sub-summary value in the summary field.

Thank you Comment! I was sorting by too many fields. Thanks again for the help.

[Reid]

Another problem has arisen. Since I changed the sorting in order to get the category totals I lost my other subtotals at the bottom because they are based on values calculated by the GetSummary function. Is there a way to keep those totals?

[comment]

If I understand correctly your screenshot, you should have two sub-summary parts - one by category and one by part number - and sort the records by part number AND by category (in this order).

[Reid]

If I understand correctly your screenshot, you should have two sub-summary parts - one by category and one by part number - and sort the records by part number AND by category (in this order).

Yes I have the subsummary parts in the correct order, however, the problem is that one of the calculations is based on a GetSummary field that is calculated based on Serial Number. So when I sort by Part Number and Category in order to get a total of the parts for each category I lose the other totals.

I attached screenshots to explain better if needed.

[comment]

one of the calculations is based on a GetSummary field that is calculated based on Serial Number.

Sorry, I don't follow. Doesn't each record have a unique serial number? If so, using it as the breakfield in GetSummary() makes no sense.

The screnshots tell me nothing, I'm afraid.

[Reid]

Sorry, I don't follow. Doesn't each record have a unique serial number? If so, using it as the breakfield in GetSummary() makes no sense.

The screnshots tell me nothing, I'm afraid.

Sorry for the confusion. When I use the term Serial Number I'm not referring to the ID used in FM. I'm referring to the Serial Number given to a specific container of parts used to track data.

[comment]

I am still confused: what does a record in your table represent, and how many groups does it belong to, in what order?

In case the question isn't clear: say I have a table of people, where each person belongs to a city, state, country and continent. If I sort the records by continent, country, state and city, I can get any sub-summary value I need - either by using a sub-summary part containing a summary field, or by a calculation using the GetSummary() function.

[Reid]

I am still confused: what does a record in your table represent, and how many groups does it belong to, in what order?

In case the question isn't clear: say I have a table of people, where each person belongs to a city, state, country and continent. If I sort the records by continent, country, state and city, I can get any sub-summary value I need - either by using a sub-summary part containing a summary field, or by a calculation using the GetSummary() function.

Each record in this table represents a specific defect and quantity. The Main table is Inventory. Inventory is connected to a Sort Data table (MFSD). The Layout with the Sort Data table has a portal that shows individual defect items from the MFSDItems table (the one I'm using to make this report). The "PQS" used to connect them is the ID created by the Inventory table.

Sorry, hopefully I explained myself better...attached is a screen capture of the two tables in question (the main inventory table is not shown).

[comment]

hopefully I explained myself better...

I am afraid not.. Please leave the tables aside for a moment and explain the real-life situation in the same terms as in my example, i.e.:

Person -> Defect

City ->

State ->

Country ->

...

[Reid]

I'm not sure I understand what groups you are talking about. The records in the table represent a specific defect and quantity. They are at the very bottom of the organizational structure.

This is the only way I know how to explain it.

Record = single defect

Batch/Serial Number contains various defect records

Part Number contains various Batch/Serial Number Records

Date contains various Part Numbers that were inspected for a specific date.

[comment]

OK, then if you sort your records by Date, Part and Batch then GetSummary (sTotalQty ; Batch ) will return the quantity of defects in the batch.

By "group" I mean a group of records that share a common attribute. Sorting records by this attribute results in all records with the same value being grouped together.

[Reid]

Yes I am able to calculate the total number of defects. The problem is that I also want the total number of batch quantities on the same report. (See thumbnails from Post #9). So within that part number were a bunch of batch quantities. I want the report to show the total of the batch quantities. I can't do that using the same calculation as before because the calculation was calculated from the sum of a GetSummary field based on Batch Number.

I guess the point is that if you have a GetSummary calculation, you have to sort based on the same field in that calculation. So I need to find a different way of calculating the Batch/Serial Totals without using a GetSummary calculation based on the Batch Number.

[comment]

if you have a GetSummary calculation, you have to sort based on the same field in that calculation.

That is correct.

So I need to find a different way of calculating the Batch/Serial Totals without using a GetSummary calculation based on the Batch Number.

I still don't get why you cannot include the Batch/Serial in the sort order.

[Reid]

That is correct.

I still don't get why you cannot include the Batch/Serial in the sort order.

If I include the Batch/Serial in the Sort Order the report doesn't total up the defects into individual categories (See attachment to post #1). It gives me a list of each Batch and its defects instead of combining them all together.

[comment]

I am completely lost. Why don't you post some data, alongside what your report should look like using that data.

[Reid]

I am completely lost. Why don't you post some data, alongside what your report should look like using that data.

I just tried to upload the data but it appears I can't upload a FM file or an excel file format. How should I post it then?

[comment]

Zip your file before you try to upload.

[Reid]

Thanks,

Attached is a sample database that has the records that need to be sorted. I also attached an excel file that has the same raw data and an example of the report I am trying to make. The difficulty I am having is with the Total Batch Qty. See note in excel.

Thanks for the help!

SampleFile.zip

[comment]

This would be easy with proper relational structure - see attached.

Note however, that relationships ignore the found set. This could be problematic if you wanted to limit your report to certain defect types only, for example. If that's an issue, then a more complex solution will be required.

Defects.zip

[Reid]

This would be easy with proper relational structure - see attached.

Note however, that relationships ignore the found set. This could be problematic if you wanted to limit your report to certain defect types only, for example. If that's an issue, then a more complex solution will be required.

You make it look so easy! I didn't realize you could have a calculation to sum a value from a different table and when a report is run FM only sums the ones being used in the sub-summary of the report.

[comment]

when a report is run FM only sums the ones being used in the sub-summary of the report.

Actually, that's not what happens. All related records are summed - hence the warning about possible discrepancies between found set and related set.

[Reid]

Could a possible solution be to create a new field in the Batch table that calculates only one value for each Batch Number?

Something like

Case ( BatchNo = SameEntries::BatchNo ; BatchQty )

[comment]

No, this would face a similar problem: suppose the first related record in SameEntries happens to be omitted from the current found set in Defects.

I believe the method suggested in my demo will work in most cases - I am just not sure what type of variations on your report you are likely to produce.