Find Script to get the most profitable sales

[n7mafia]

I was wondering how to accomplish that. I got a set of records, each with an item name and type, the purchase and sale price and the calculated net profit. I would like to make a script that finds the 10 most profitable sales and display them on a repeated field (?) (I am not sure about the last part). Anyone can point me towards the right lines of code. I am very new to scripting unfortunately.

I tried this, where "T_ItemName" is supposed to be the repeating field (10) that should display the top ten, but it doesn't work:

[*]Perform Find [ Specified Find Requests: Find Records; Criteria: D3 Database::DE_Sale Price: “>1” ] [ Restore ]

[*]Sort Records by Field [ Descending; D3 Database::DE_Profit ]

[*]Replace Field Contents [ D3 Database::T_ItemName; Current contents ] [ No dialog ]

Thanks in advance.

[doughemi]

You don't want to use repeating fields! They are a vestigial remainder from before FileMaker was relational. They have their place, but this is not it.

Create a new Table Occurrence of your table called D3Profits. Relate it to your original table with a Cartesian (X) relationship. (With a Cartesian relationship, the fields related don't matter. The relationship finds all the records in the related TO.). Sort the relationship by DE_Profit (descending).

Now create a portal to D3Profits in your original layout and set it to show 10 rows. Populate the portal with D3Profits::T_ItemName. You could also put D3Profits::DE_Profit in the portal, so you can see not only the items, but their profit margins.

[n7mafia]

Okay, I think I got it right, up until a certain point at least.

I created a new table (D3 Profits) and connected it via relationship to the other table (D3 Database) and I sorted it by DE_Profit. I also created a portal with the relevant fields but:

1) It appears to not displaying anything, should I add a find script somewhere? How to I populate it? I get an <Index Missing> error in each field and if I click it I get a "This operation cannot be performed because one or more of the relationship between these tables are invalid".

3) Along with the fields I got a grey rectangle which displays "D3Profits [1..10, Sort]", what should I use it for exactly?

Thanks for the patience and excuse my many questions but I am totally new to portal and relationships as well and I am trying to un-addict myself from repeating fields :)

[comment]

I would like to make a script that finds the 10 most profitable sales

How about:

Show All Records

Sort Records [by profit, descending]

Go to Record [11]

Omit Multiple Records [ Get ( FoundCount ) -10 ]

[n7mafia]

How about:

Show All Records

Sort Records [by profit, descending]

Go to Record [11]

Omit Multiple Records [ Get ( FoundCount ) -10 ]

I should attach this script to the aforementioned portal and relationship or to an entirely new set of fields that have nothing to do with the portal?

[comment]

You should run the script in a list layout of your existing table. There is nothing to add.

[n7mafia]

It works, thanks man.

[n7mafia]

I was wondering, is there any way, on the same layout to add up another "top10" based on a different sort order, or perhaps add up a graph based on a different sort order, would that be possible with a scripting solution or am I limited to having to use a different layout for every different sort order?

[comment]

At the same time?

[n7mafia]

At the same time?

Yep, at the same time, on the same layout, is it possible?

[comment]

It'd possible, but a different method is required - Doug already described most of it.

You need a self-join relationship using the x relational operators (between any two fields). Do not add any new tables - just place another occurrence of the same table on the graph.

Use a 10-row portal for each group of records, and sort each portal by another field (no need to sort the relationship).

Another option (in v.12) is to show each sorted group in a a calculation field, using the Execute SQL function. Though a portal is a neater display method, IMHO.

Keep in mind that no matter which method you use, it's not going to win any speed contests.

[n7mafia]

I am having a problem with the portal part. I managed to create a self-join relationship but when I created the portal, specified the sort order and chose to display ten lines all the portal does is displaying the first record in the database, not even sorted. I feel I am missing something, somewhere. Do I have to tell the portal to "Find all records" first? Or is it something else entirely?

Also, I wonder, can the portal system be used to display charts based on different sort orders?

[doughemi]

Sounds like the relationship is still an = relationship, not an X. In FM10, it looks like this:

Sorry, I've only got 10 so I can't help you with charting

[comment]

can the portal system be used to display charts based on different sort orders?

It's possible, but it would be rather awkward. How often do you need to display this, and how often it needs to be updated? Perhaps you should have your startup script collect the values into variables (sorting the records several times in succession) and use this for your charts? Otherwise you may find yourself adding quite a few resources (calculation fields and/or relationships) to your schema.

[n7mafia]

Sounds like the relationship is still an = relationship, not an X. In FM10, it looks like this:

Sorry, I've only got 10 so I can't help you with charting

You were right, I missed that step. I am still missing something though since the portal:

- Displays a random record rather than the most profitable sale

- Displays still only one line, even though I specified the number of rows to be 10

Perhaps you should have your startup script collect the values into variables (sorting the records several times in succession) and use this for your charts? Otherwise you may find yourself adding quite a few resources (calculation fields and/or relationships) to your schema.

Problem is that different charts would require different sort order to display properly, for instance while a "Most profitable item category" stacked columns chart should be sorted by "DE_Profit"; the "Average Profit" bar chart should be sorted by "s_Profit" and if the sort order is just ONE, one chart looks all right while the other, even if the data are displayed correctly, looks awkwardly random displayed.

[comment]

The idea is roughly this:

Sort the records by one field, and write the first 10 names/values into variables. Then sort by another field and again write the first 10 records into another pair of variables. Point your charts to draw their data from the variables.