Jump to content

Exporting data - highest value in each category


bclark
 Share

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

Recommended Posts

 

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

Link to comment
Share on other sites

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 …?  :cry:

Link to comment
Share on other sites

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 by comment
  • Like 2
Link to comment
Share on other sites

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

Link to comment
Share on other sites

… 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 …?  :cry:

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

Link to comment
Share on other sites

... 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 …?  :cry:

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

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2626 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
 Share

  • Similar Content

    • By DPaquin
      Hello,
       
      I do want to upgrade from Filemaker Server 12 to version 16.
      At first I've wanted to uninstall v12 but cannot access to the console. 
      I get screen saying "Cannot start the Consol, managing  Admin Server not available?
      Does anyone have an idea of why I cannot start the FMS 12 Consol.
       
      Thanks!
       
      FMS-ERR.tiff
    • By candell
      FYI I tried to add this to the Filemaker community page but don't think it worked hence adding here also.
      I have been tasked with creating a website for a charity, part of the website will be a searchable archive of all of their historic data. This data is stored in Filemaker Pro 12 on the historians machine.
      He has provided me with 4 .fmp12 files, I downloaded a trial version that I found here http://www.filemaker.com/redirects/ss.html?file=PRO12MAC however none of his files will open.
      The website runs on Wordpress which is php based, the database is a maria database although I am happy to use whatever method I need to get this data on to the website and searchable.
      I was expecting to open the files in the trial version, export as csv and create and import in to the maria database.
      Is this possible with filemaker, do you think I would be better downloading a trial of the latest version, will his files open ok in the latest version?
      Unfortunately I have no experience of filemaker so am struggling a little.
      Thanks for any advice anyone may be able to provide.
    • By JH Howland
      Filemaker Pro Adv 12 running under Windows 10.
      Trying to "Send Mail" with two (2) attachments.  The specified attachment files are defined as $Path1 (from_map.png) and $Path2 (to_map.png).  Both files are located at C:/Users/{user id}/AppData/Local/Temp/S<n>.
      The Specified files are entered as:
      $PATH1
      $PATH2
      However, when Send Mail is activated only $PATH1 is shown as an attachment.
      Can't seem to get past this problem, help please.
    • By Kishan_Canoo
      I would like to have options to enter either fields (selling price or margin) and the other should be automatically calculated for instance:
          
           Cost Price = 100 (Available)
           Sell Price  =  Calculation field or ?? Input amount
           Margin     =  Calculation field or ?? Input percentage
          
           If i put 160 on sell price it should automatically put 37.5% in margin or if i put 37.5% in margin field it should automatically fill sell price for 160.
      File uploaded
      Login name: admin
      No Password: 
       
      Thank you in advanced.
      TEST.fmp12
    • By John Link
      I have FileMaker Pro 12 installed on four computers on my network, and FileMaker Pro Advanced 12 stored on a fifth computer. There is a shared database stored on one of the first four computers. When using the shared database from the fifth computer and I attempt to check any of the checkboxes for the field Events, I see the message shown in the attachment and I am unable to check the checkbox (This behavior occurs only on the fifth computer). If I then do a search, after doing the search I am able to check any of the checkboxes in the previously problematic field. I've restarted the fifth computer and the problematic behavior stills occurs.

      Any suggestions?

×
×
  • Create New...

Important Information

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