Exporting data - highest value in each category

[bclark]

 

Hi folks,

As a relative newcomer to FMP, I am trying to export some records and am having difficulty.....

2 of the tables in my database are:

 

MANUFACTURER [mName, contact email, contact name]  (approx 50 records)

PRODUCT [pID, pName, description, price, mName]  (approx 300 records)

 

So, a manufacturer makes many products.....

 

I want to export each manufacturers most expensive product, but only the top 10.

This is probably quite straight forward, but I just cant quite get my head around it!  Any help appreciated.  I am using FMP 12

 

Many thanks

 

Brian

[Lee Smith]

Automatic message

This topic has been moved from "FileMaker Pro 12" to "Importing & Exporting".

[eos]

Automatic message

This topic has been moved …

 

… while I was writing a reply; with the result that on pressing “Post reply”, the original reference/link/path had vanished – "The topic doesn't exist!" – and I had lost my most recent changes.

 

Bug or feature …? 

[comment]

I want to export each manufacturers most expensive product, but only the top 10.

This is probably quite straight forward, but I just cant quite get my head around it! 

 

No, I am afraid this is not straightforward at all. Try scripting it this way (performed from a layout of the Products table):

Show All Records
Sort Records [ << sort by mName and by price, ascending(!) >> ]
Go to Record [ First ]
Loop
 Omit Multiple Records [ GetSummary ( Products::sCount ; Products::mName ) - 10 ]
 Set Variable [ $nextRecNum; Value:Get ( RecordNumber ) + GetSummary ( Products::sCount ; Products::mName ) ]
 Exit Loop If [ $nextRecNum > Get ( FoundCount ) ]
 Go to Record/Request/Page [ $nextRecNum ]
End Loop

where Products::sCount is a summary field (count of pID).

 

This will create a found set of top 10 records in each category, which you can then export.

 

 

---

Note:

1. You should use a ManufacturerID as the matchfield for the relationship, instead of a name.

 

2. With 50 manufacturers and 300 products, you are talking about an average of 6 products per manufacturer; it doesn't look like extracting the 10 most expensive products will make much of a difference.

Edited May 22, 2014 by comment

[bclark]

Thats really helpful! Thanks very much

 

However, this is my fault, but what I am trying to achieve is a list that contains the most expensive product from each manufacturer eg

 

manufacturer1      most expensive product    

manufacturer2      most expensive product     

manufacturer3      most expensive product

manufacturer4      most expensive product

 

so only one occurence of the manufacturer name appears in the list.....

....  hope that makes some sense!

 

I was trying to use the max function to this.  I did find something similar in access here - http://www.databasedev.co.uk/access_max_function.html - and tried to translate into FMP, but to no avail.....

 

 

 

Again, really appreciate your help

[Lee Smith]

… while I was writing a reply; with the result that on pressing “Post reply”, the original reference/link/path had vanished – "The topic doesn't exist!" – and I had lost my most recent changes.

 

Bug or feature …? 

It has happened to me in the past also. I’ll report this to Ocean West.

[bclark]

... and limit that list to the 10 most expensive products......

… while I was writing a reply; with the result that on pressing “Post reply”, the original reference/link/path had vanished – "The topic doesn't exist!" – and I had lost my most recent changes.

 

Bug or feature …? 

Sorry to hear that - I appreciate you tried to help though

[comment]

However, this is my fault, but what I am trying to achieve is a list that contains the most expensive product from each manufacturer eg

 

manufacturer1      most expensive product    

manufacturer2      most expensive product     

manufacturer3      most expensive product

manufacturer4      most expensive product

 

so only one occurence of the manufacturer name appears in the list.....

 

Actually, the script is generic and can be adjusted to any "top N records in category" scenario - so if you just change the - 10 part to - 1, you will get exactly the result you describe now.

[bclark]

Actually, the script is generic and can be adjusted to any "top N records in category" scenario - so if you just change the - 10 part to - 1, you will get exactly the result you describe now.

Thats awesome

[Helpful Harry]

The Script is the best and most efficient option, especially if it's something you don't need to do "a lot".

You could also create a new Relationship link from Manufacturer to Product that is reverse-sorted by Price. Then a simple set of Calculation Fields in the Manufacturer Table could grab the first, and therefore highest priced, related Product information via the Relationship.

e.g.

HighestPrice = Relationship::Price

HighestpName = Relationship::pName

etc.

Those Fields can then easily be exported from the Manufacture Table along with any other Maufacturer Record data.

[comment]

I have a rule against adding relationships and/or fields that serve no purpose other than export.

[bclark]

Hi folks - thanks for all the help! However,  am now trying to return the highest value order each customer has made.

 

I have an order table with orderID, custID, orderTotal (where orderTotal is a summary field)

Also other tables with customer data, orderItems data, products etc....

 

I have managed to produce reports that list each customer and their orders, in descending order of orderTotal using subsummary parts  eg the report works well and generates content like.....

 

Customer ID : 213    Customer: Mr Smith

Order Date   12/08/13     Order Total   £345.66

Order Date   09/10/13     Order Total   £79.36

Order Date   01/05/14     Order Total   £33.99

 

Customer ID : 673   Customer: Mrs Jones

Order Date   03/07/13      Order Total   £49.99

Order Date   11/11/13      Order Total   £9.95

 

etc

 

What I am trying to do is just identify the highest order total for each customer.  I thought  could use the max summary function, but i cant seem to create a summary field that max's the ordertotal summary field. Ideally, Id like the report to just show....

 

Customer ID : 213    Customer: Mr Smith      Highest Value Order  £345.66

Customer ID : 673   Customer: Mrs Jones     Highest Value Order    £49.99

 

etc....

 

I tried to modify the examples above, but to no avail.  AGain, any help appeciated!

 

Brian

[comment]

Could you clarify this point?

 

I have an order table with orderID, custID, orderTotal (where orderTotal is a summary field)

 

That doesn't make much sense, I am afraid. A summary field of what exactly?

[bclark]

sorry, the orderItem table contains a summary field to  calculate the total order value orderItem(orderItemID, productD, quantity, subtotal -[quantity * product.price] and orderTotal [summary total of subtotal])

[comment]

The easy solution here is this:

 

1. Define a calculation field cOrderTotal in the Orders table =

Sum ( OrderItems::SubTotal )

2. Define another calculation field in the Customers table =

Max ( Orders::cOrderTotal )

Note that this will return the amount of the highest order - but not any details regarding the relevant order itself. For this, you would have to either:

 

a. produce the report from the Orders table, sorted by CustomerID and by cOrderTotal, then omit in each group all orders except the highest one in the group (very similar to the script in post #4 above);

 

or:

 

b. produce the report from the OrderItems table in the same manner, just working at two levels (and you would need a calculation field in the OrderItems table to get the CustomerID from Orders, since GetSummary() cannot work with related fields directly).

 

 

If this is for display only, you could also use a one-row portal to Orders on a layout of Customers, sorted by cOrderTotal, descending.