oneillrj@pbapi.com

How to total sub sets of data

Discussion created by oneillrj@pbapi.com on Jun 13, 2018
Latest reply on Jun 14, 2018 by oneillrj@pbapi.com

Windows

FileMaker 16 Adv

Server 2008

 

Our work order system creates a work order for each job, of course. Now, I want to pull the names of each customer who did business with us but I only need it one time.

 

I can pull each work order and capture the data I need but there are multiple work orders for each of the customers who did business.

 

Because this is simply a new layout with a subset of the work order I can't find the data I want and delete the rest.

 

I could export the data I want to a new table and work with it from there... OR....

 

Is there another way to consolidate multiple records of the same customer to a single record?

 

I could script this....

 

Here's what I'm thinking.

 

Create a new table with fields for the information I need.

 

Write a script that will search the data for the time frame I want to pull the data into a found set.

Then sort the found set based on customer number.

Then loop through the data, setting a variable to the first customer number and if the second record customer number matches, skip to the next one until the customer numbers don't match.

Mark each found record that is unique.

When the loop is finished, search for each of the records that have the mark and import them into the new table.

 

Or skip the need for a new table and work with the resulting marked data in a new layout.

 

Are there any other 'better' ways to do this as in a calculation I'm not aware of?

 

Thank you for your time.

Outcomes