Jump to content

Combine Like Records


Reid

This topic is 4789 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

How to Combine.JPG

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Capture Report View.JPG

Capture Report View2.JPG

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Capture Tables.JPG

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 )

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 4789 days old. Please don't post here. Open a new topic instead.

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
×
×
  • Create New...

Important Information

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