AnsweredAssumed Answered

Numbering a field based on highest number of another field

Question asked by CW on Feb 14, 2011
Latest reply on Feb 16, 2011 by philmodjunk

Like • Show 0 Likes0
Comment • 9

Title

Post

Hi,

I created a database for a stock photographer that will show his best selling photographs. The image below is what i have so far, the images are the current top 4 selling images.

What i am trying to do is put in a field that will give each of these a ranking number based on the Total payable field.

The quantity number that is positioned to the left of the the image thumbnail is telling you how many times the image has sold. The baby has sold a total of 14 times and the total payable field that is on the far right of the screen is calculating all 14 sales together.

The rank field would say that the baby picture is ranked #1 and then the next would be ranked #2 and so on. I need the ranking to change based on the search criteria. For example, in this search, the baby is ranked #1 because it has the highest Total Payable.

If I do a search that is for a different time period, another image might have a higher total payable, and therefore would be ranked #1, and so on. So the rankings change based on the search time frame because the total payable sales changes based on the time frame chosen for the search.

Anything you can do to help would be appriciated

Thanks,

Carl

philmodjunk Feb 14, 2011 1:44 PM

Correct Answer

Try this:

Find your top records
Sort them in Ranking order
Use Replace field contents with the serial numbers option to load a rank # field with a serial number

Now you have a rank that won't change until you do the above steps again (and this can be scripted).

I'd also give serious consideration to a seprate images table where you have only one record for each image. The above table would then refer to it by an ImageID number so that multiple records of this image selling would all link to the same image record. I'd put the rank number field in this related, one record for each unique image) table so that it will be visible no matter which record for the same ranked image appears in your layout shown above.

See the reply in context

No one else had this question

Outcomes

philmodjunk Feb 14, 2011 11:14 AM

Will you do anything more with this rank than display or print it?

You can sort your found records in the ranking order and place @@ as layout text in the record body and this will display the record number--which just so happens to match your ranking number if you've sorted your records accordingly. That may be all that you need here.

Actions

CW Feb 14, 2011 1:05 PM

I will only use the rank as a display. Setting my ranking order and placing the @@ as layout text works for when i sort by the top sellers. That is helpful but I am trying to get the rank to be accurate even when I'm sorting by a file name or date range.

Thanks

Actions

Sorbsbuster Feb 14, 2011 1:12 PM

Capitalising on Phil's (brilliantly economical) idea, you always have buttons to let the user sort, yes?

- Create a field called Ranking

- Set your SortBy[Whatever] script to sort by the ranking first of all

- The next script step sets the Ranking field to be the record number (as per Phil's idea)

- the next script step sorts it the way you want.

- display the the Ranking field on the resulting screen.

Actions

CW Feb 14, 2011 1:39 PM

Sorry, I left out some key information.

Yes, I have sort buttons and thanks for the help what you and Phil are saying works great. but I forgot to explain the problem I am having when doing the ranking by @@.

I have file names and images that are duplicates. so every time an image sells it will have the same thumbnail and same image name adding more and more each time the image sells.

Here is what happens, because i have multiple images and file names. I have a search that will go through and get rid of all the duplicates but one image so we can see our top sellers with out all the extra images.

When i don't run the search that gets rid of all of the duplicates the rank is not correct because it counts the same image multiple times. the baby images should all be ranked #1 but FM see's them as different items not as one collection.

If this image was the top seller i would want the rank to be #1 for all of the lines, not 1 - 4.

I hope that makes more sense.

Thanks so much.

Actions

philmodjunk Feb 14, 2011 1:44 PM

Try this:

Find your top records
Sort them in Ranking order
Use Replace field contents with the serial numbers option to load a rank # field with a serial number

Now you have a rank that won't change until you do the above steps again (and this can be scripted).

Actions

CW Feb 14, 2011 2:28 PM

Thanks!

That helps alot, I'm very new with FMPRO.

I created the seprate image table.

now i have an image table and a sales table. I'm not sure how to get my multiple records with the ImageID to connect to the thumbnail image on a diffrent table.

Actions

philmodjunk Feb 14, 2011 2:34 PM

Check out this simplified Invoicing demo file created by Comment and uploaded to a different FileMaker Forum:

http://fmforums.com/forum/showpost.php?post/309136/

Your Image table would be the Product table in this demo file. Your sales table would be the LineItems table in this demo file.

Note how an invoice can list multiple items in the portal of LineItems records and how you use a drop down menu to select items (images in your case) that were sold on that invoice.

Actions

CW Feb 16, 2011 8:39 AM

Thanks Phil,

Your advice was just what i needed.

I do however have another problem. Because i have mutiple sales in diffrent months linking to one image, when i do a find by month, FMPRO will bring up the sale for that month in the portal along with all the information for that image even if it is not the month i am trying to find.

Is there any way to, lets say, search for January and only have the sales in january pop up in the portal?

The image above is what i get after doing a find for February. I only want the three February sales to pop up.

Thanks again for your help.

Actions

philmodjunk Feb 16, 2011 8:47 AM

It's important to understand what you are really telling FileMaker to do when you enter find mode and then specify search criteria in the fields of a portal. When you do this, you are telling FileMaker: "Find all records in the Parent (Your image table in this case I think), table that have at least one related portal record matching this criteria." Once you initiate the find, Filemaker finds all those records and displays them in the found set. Each such record then displays all the portal records as controlled by the relationship and portal settings you set up--not just the portal records that matched your criteria.

If you want to see only the portal records that match your criteria, you have two options:

1) Do the search on a layout based on the portal table instead of the parent table. Fields from the parent record can be placed in a header or sub summary part if you need that info. (See the invoice print layout in the demo file for an example of this.)

2) If you are using FileMaker 11, you can add a portal filter expression that limits the listed line items to just those that match your criteria (such as a specific month.)

Actions

9 Replies

philmodjunk Feb 14, 2011 11:14 AM
Mark Correct
Correct Answer
Will you do anything more with this rank than display or print it?

You can sort your found records in the ranking order and place @@ as layout text in the record body and this will display the record number--which just so happens to match your ranking number if you've sorted your records accordingly. That may be all that you need here.
Like • Show 0 Likes0
Actions
CW Feb 14, 2011 1:05 PM
Mark Correct
Correct Answer

I will only use the rank as a display. Setting my ranking order and placing the @@ as layout text works for when i sort by the top sellers. That is helpful but I am trying to get the rank to be accurate even when I'm sorting by a file name or date range.

Thanks
Like • Show 0 Likes0
Actions
Sorbsbuster Feb 14, 2011 1:12 PM
Mark Correct
Correct Answer
Capitalising on Phil's (brilliantly economical) idea, you always have buttons to let the user sort, yes?

- Create a field called Ranking

- Set your SortBy[Whatever] script to sort by the ranking first of all

- The next script step sets the Ranking field to be the record number (as per Phil's idea)

- the next script step sorts it the way you want.

- display the the Ranking field on the resulting screen.
Like • Show 0 Likes0
Actions
CW Feb 14, 2011 1:39 PM
Mark Correct
Correct Answer
Sorry, I left out some key information.

Yes, I have sort buttons and thanks for the help what you and Phil are saying works great. but I forgot to explain the problem I am having when doing the ranking by @@.

I have file names and images that are duplicates. so every time an image sells it will have the same thumbnail and same image name adding more and more each time the image sells.

Here is what happens, because i have multiple images and file names. I have a search that will go through and get rid of all the duplicates but one image so we can see our top sellers with out all the extra images.

When i don't run the search that gets rid of all of the duplicates the rank is not correct because it counts the same image multiple times. the baby images should all be ranked #1 but FM see's them as different items not as one collection.

If this image was the top seller i would want the rank to be #1 for all of the lines, not 1 - 4.

I hope that makes more sense.

Thanks so much.
Like • Show 0 Likes0
Actions
philmodjunk Feb 14, 2011 1:44 PM
Unmark Correct
Correct Answer
Try this:
1. Find your top records
2. Sort them in Ranking order
3. Use Replace field contents with the serial numbers option to load a rank # field with a serial number
Now you have a rank that won't change until you do the above steps again (and this can be scripted).

I'd also give serious consideration to a seprate images table where you have only one record for each image. The above table would then refer to it by an ImageID number so that multiple records of this image selling would all link to the same image record. I'd put the rank number field in this related, one record for each unique image) table so that it will be visible no matter which record for the same ranked image appears in your layout shown above.
Like • Show 0 Likes0
Actions
CW Feb 14, 2011 2:28 PM
Mark Correct
Correct Answer
Thanks!

That helps alot, I'm very new with FMPRO.

I created the seprate image table.

now i have an image table and a sales table. I'm not sure how to get my multiple records with the ImageID to connect to the thumbnail image on a diffrent table.
Like • Show 0 Likes0
Actions
philmodjunk Feb 14, 2011 2:34 PM
Mark Correct
Correct Answer
Check out this simplified Invoicing demo file created by Comment and uploaded to a different FileMaker Forum:

http://fmforums.com/forum/showpost.php?post/309136/

Your Image table would be the Product table in this demo file. Your sales table would be the LineItems table in this demo file.

Note how an invoice can list multiple items in the portal of LineItems records and how you use a drop down menu to select items (images in your case) that were sold on that invoice.
Like • Show 0 Likes0
Actions
CW Feb 16, 2011 8:39 AM
Mark Correct
Correct Answer
Thanks Phil,

Your advice was just what i needed.

I do however have another problem. Because i have mutiple sales in diffrent months linking to one image, when i do a find by month, FMPRO will bring up the sale for that month in the portal along with all the information for that image even if it is not the month i am trying to find.

Is there any way to, lets say, search for January and only have the sales in january pop up in the portal?

The image above is what i get after doing a find for February. I only want the three February sales to pop up.

Thanks again for your help.
Like • Show 0 Likes0
Actions
philmodjunk Feb 16, 2011 8:47 AM
Mark Correct
Correct Answer
It's important to understand what you are really telling FileMaker to do when you enter find mode and then specify search criteria in the fields of a portal. When you do this, you are telling FileMaker: "Find all records in the Parent (Your image table in this case I think), table that have at least one related portal record matching this criteria." Once you initiate the find, Filemaker finds all those records and displays them in the found set. Each such record then displays all the portal records as controlled by the relationship and portal settings you set up--not just the portal records that matched your criteria.

If you want to see only the portal records that match your criteria, you have two options:

1) Do the search on a layout based on the portal table instead of the parent table. Fields from the parent record can be placed in a header or sub summary part if you need that info. (See the invoice print layout in the demo file for an example of this.)

2) If you are using FileMaker 11, you can add a portal filter expression that limits the listed line items to just those that match your criteria (such as a specific month.)
Like • Show 0 Likes0
Actions

Numbering a field based on highest number of another field

Title

Post

Outcomes

Related Content

Recommended Content