9 Replies Latest reply on Feb 16, 2011 8:47 AM by philmodjunk

    Numbering a field based on highest number of another field

    CW
    CW Feb 14, 2011 11:06 AM

      Title

      Numbering a field based on highest number of another field

      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

      • 214 Views
      • Tags:
        • 1. Re: Numbering a field based on highest number of another field
          philmodjunk
          philmodjunk Feb 14, 2011 11:14 AM (in response to CW)

          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.

          • 2. Re: Numbering a field based on highest number of another field
            CW
            CW Feb 14, 2011 1:05 PM (in response to CW)

            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

            • 3. Re: Numbering a field based on highest number of another field
              Sorbsbuster
              Sorbsbuster Feb 14, 2011 1:12 PM (in response to CW)

              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.

              • 4. Re: Numbering a field based on highest number of another field
                CW
                CW Feb 14, 2011 1:39 PM (in response to CW)

                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.



                • 5. Re: Numbering a field based on highest number of another field
                  philmodjunk
                  philmodjunk Feb 14, 2011 1:44 PM (in response to CW)

                  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.

                  • 6. Re: Numbering a field based on highest number of another field
                    CW
                    CW Feb 14, 2011 2:28 PM (in response to CW)

                    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.

                    • 7. Re: Numbering a field based on highest number of another field
                      philmodjunk
                      philmodjunk Feb 14, 2011 2:34 PM (in response to CW)

                      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.

                      • 8. Re: Numbering a field based on highest number of another field
                        CW
                        CW Feb 16, 2011 8:39 AM (in response to CW)

                        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.

                        • 9. Re: Numbering a field based on highest number of another field
                          philmodjunk
                          philmodjunk Feb 16, 2011 8:47 AM (in response to CW)

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