7 Replies Latest reply on Oct 17, 2013 8:21 PM by erickaczmarczyk

    Sorting Based on Repetitions

    erickaczmarczyk

      Currently there are two tables in my database; one for inventory, one for images.

       

      The inventory table has a field for inventory numbers and one for image numbers (this is set up as a repetition so multiple images can be referenced.

      The image table has a field for image numbers and a container for the image files.

       

      The goal is to reference the images and sort based on the order in the repetition field.

       

      Please see image attached, it is a visual of how it is functioning now and how I would like it to work.

      The image is from the inventory table. The portal on the left displays the referenced images. The repeating field on the right is the list of referenced image numbers. What I am looking to do is to have the images in the portal to sort as the reference image numbers are listed in the field on the right.

       

      Right now the images are not sorted, they are displayed as default 1, 4, 8, 10. In this example, I would like them to sort 10, 8, 1, 4 based on the repetition numbers, 1, 2, 3, 4.

       

      Any suggestions about how to approach this would be much appreciated.

       

      Thanks!

        • 1. Re: Sorting Based on Repetitions
          IT_User

          Well, you could use the List function on your repeating field,

          and have that list in a Value List (by another calculation field with the List function),

          then sort your portal by the Value List.

           

          I haven't personally tried sorting by a value list to know how it exactly works.

           

          & Just curios, are you rearranging the numbers in the repeating field manually (without buttons or scripts)?

          • 2. Re: Sorting Based on Repetitions
            erickaczmarczyk

            Thanks for the reply, that's similar to what I was trying to work out.

             

            What is in the screenshot is just the hidden function, there will be no numbers visible in the final product.  It will be replaced with buttons and a drag and drop kind of sorting.

            • 3. Re: Sorting Based on Repetitions
              DavidJondreau

              You can use an unstored calculation field with a numeric result in the child table and sort on that field.

               

              That calculation would look something like:

               

              Let([

              repeating = List ( table::repeating ) ;

              this.val = index.field ;

              result = ValuePosition ( repeating ; this.val ; 1 )

              ];

              result

              )

               

              A couple caveats with this code...ValuePostion() is a handy custom function that's easily replicated with regular old code (using Position(), Left() and ValueCount() ). And the List() on the repeating field won't return the needed result if you blank repetitions.

              • 4. Re: Sorting Based on Repetitions
                Stephen Huston

                Just curious as to the reason for choosing a repeating field instead of related records in a portal, where portal records  could be sorted by a number field, even if the number/field is not shown in the portal.

                 

                (This is the third question I've seen in a week about using repeating fields in situations which seem to beg for related records, so I had to ask, why?)

                • 5. Re: Sorting Based on Repetitions
                  erickaczmarczyk

                  The repeating field is to list the related records that are shown in the portal.

                  There are two tables, "inventory" and "images" where the records are being related from.

                  The repeating field is to list image numbers and to help with sorting... especially when images show up on multiple records in inventory and the user wants them sorted differetly on each.

                  • 6. Re: Sorting Based on Repetitions
                    erickaczmarczyk

                    Going to make an attempt now with David's suggestion now.

                     

                    Thanks for the help everyone!

                    • 7. Re: Sorting Based on Repetitions
                      erickaczmarczyk

                      Got it working with this as a calculation field on the image table... however next big step is to figure out how to have different image sorts based on inventory number.

                       

                      You'll see in attached screenshots that inventory record number two is sorting based on number one.

                       

                      Let([

                      list = List(inventory::Reference Image Number);

                      image = Image Number;

                      sort = ValueCount ( Left(list ; Position(¶&list&¶;¶&image&¶;1;1)) )

                      ];

                      sort

                      )

                       

                      thanks for the help thus far!

                       

                      Screen Shot 2013-10-17 at 11.19.06 PM.pngScreen Shot 2013-10-17 at 11.19.13 PM.png