13 Replies Latest reply on Oct 6, 2013 3:39 AM by tony.dhoop@telenet.be

    how to INSERT (not add) a record in a portal?

    tony.dhoop@telenet.be

      How to insert a row in portal data?

      Example : if a portal would look like

      A

      B

      D

      and one wants to insert "C", it would be nice to have it between "B" and "D" .

      However, Filemaker will insert it after the "D", which is inconvenient.

      A

      B

      D

      C

      (in this example sorting on name would work, but there are many other examples where it does not, where one wants a very specific, sometimes subjective, order of portal records).

      Thought about adding a field "sort" and sort on that number, giving

      A 01

      B 02

      D 03

      C

      but then the user needs to change the value of D from 03 to 04 and set C to 03 ... again, not very user friendly... (althought it would work)

      A 01

      B 02

      C 03

      D 04

      So I'm looking for a clean and easy way to insert a row between two rows of a portal.

      Any suggestions?

      Thanks!

      Tony

        • 1. Re: how to INSERT (not add) a record in a portal?
          Stephen Huston

          You are correct that sorting is the key. The trick is to make it easy and intuitive to the user. That plces the hard work on the developer.

           

          I've done this on a few rare occasions by sorting on a number assigned by scripting and adding and Up-arrow and Down-arrow to each child racord at one end of the portal row. When either button is used, you need to capture the portal row number, then renumber the record row records "portalOrder" field (loop), then go adjust the target row's number up or down (adding or subtracting by decimals works well for placing a row between two otehrs) so it will sort up or down depending on the button.

           

          This may be done with a single script and the buttons passing a $variable to the script regarding the Up/Down order.

          • 2. Re: how to INSERT (not add) a record in a portal?
            stefan_s

            Hi Tony,

             

            This is actually pretty easy.

             

            You can make the portal sorted by the field that contains the A, B, D...

             

            You can do this either in the Relationship graph or just in the portal on the layout.

             

            I would put it in the relation in the graph. There's a slight difference in behavior here.

             

            If you want to print a list of the records via a layout based on the portal row TO (Table Occurence) and you have the sort in the portal only, the records will show up in creation order, but if you have the sort in the relationsship in the graph, the records will show in the sort order directly.

             

            And you can sort by multiple fields, too.

             

            But when entering data in a portal where the records are sorted by either technique, the newly created recodr will "jump" to it's location when committing the record.

             

            But try this out.

             

            HTH

            __

            Stefan Schutt, Mouse Up, Finland

             

             

            tony.dhoop@telenet.be kirjoitti 29.6.2013 kello 18.57:

             

             

            created by tony.dhoop@telenet.be in Advanced Discussion - View the full discussion

            How to insert a row in portal data?

             

            Example : if a portal would look like

             

                 A

             

                 B

             

                 D

             

            and one wants to insert "C", it would be nice to have it between "B" and "D" .

             

            However, Filemaker will insert it after the "D", which is inconvenient.

             

                 A

             

                 B

             

                 D

             

                 C

             

            (in this example sorting on name would work, but there are many other examples where it does not, where one wants a very specific, sometimes subjective, order of portal records).

             

            Thought about adding a field "sort" and sort on that number, giving

             

                 A      01

             

                 B      02   

             

                 D      03

             

                 C

             

            but then the user needs to change the value of D from 03 to 04 and set C to 03 ... again, not very user friendly... (althought it would work)

             

                 A     01

             

                 B     02

             

                 C     03

             

                 D     04

             

            So I'm looking for a clean and easy way to insert a row between two rows of a portal.

             

            Any suggestions?

             

            Thanks!

             

            Tony

             

            Reply to this message by replying to this email -or- go to the message on FileMaker Technical Network

            Start a new discussion in Advanced Discussion by email or at FileMaker Technical Network

            Manage your email preferences.

             

            FileMaker Developer Conference 2013 • San Diego, California • August 12-15 • www.filemaker.com/devcon

             

            • 3. Re: how to INSERT (not add) a record in a portal?
              usbc

              Mr. Huston has you pointed in the right direction. However, if you have the records  sorted on a number field you don't need to re-number the records that will follow your inserted record. Your script will begin with the user clicking on record that the insertion will follow. That captures the sort number (say #22). Then as you create the new record, however you want to, stamp it that number (22+.01). While there is a technical limit, I don't think you would exceed the practical limit.

              Chuck

              1 of 1 people found this helpful
              • 4. Re: how to INSERT (not add) a record in a portal?
                tony.dhoop@telenet.be

                hey, stephan / thanks, but these techniques are not applicable to my problem.

                the example I had put ABCD was straight forward since you can indeed sort on them.

                But if you'd want to for instance sort on rainbow colors for instance (silly example, but points out what I mean), it wouldn't work

                (Rainbow colours are : Red-orange-yellow-green-blue-indigo-violet)

                 

                In a portal with

                 

                RED

                ORANGE

                GREEN

                 

                already there, how to get YELLOW between ORANGE and GREEN was my goal.

                 

                The solution of Stephen and Chuck seem to be the way to go solve it.

                 

                Thx anyway !

                Regards to Petri :-)

                • 5. Re: how to INSERT (not add) a record in a portal?
                  beverly

                  I use interface buttons to "move" portal rows up or down in sort order. Scripts 'swap' the rows by changing the sort number fields. The user does not see these fields and does not need to change anything manually.

                   

                  I suppose you could do the same with script(s) and insert above & insert below buttons for the user. Create the portal row for the user and then allow them to enter the data. Auto sort on the field will make it appear in the portal after commit if the portal is sorted.

                   

                  All possible.

                   

                  -- sent from my iPhone4 --

                  Beverly Voth

                  --

                  1 of 1 people found this helpful
                  • 6. Re: how to INSERT (not add) a record in a portal?
                    TimGriffith

                    Tony, the FileMaker API simply does not do 'insert', the nature of the API is to add a new record, thus append to the end. Every technique above is cosmetic, and will produce the illusion of inserting, but when examined the meta data will reveal that the record was created at the end of record set. This is true of all record systems- the difference is that some API's handle the 'illusion of insert' part differently.

                    • 7. Re: how to INSERT (not add) a record in a portal?

                      Hi Tony,

                       

                      If you want the  ultimate fix, then you'll need to export the data, sort it according to you needs, delete the original records, then import them in the sorted order. You could do the sorting in a fresh FM table or any spreadsheet.

                       

                      What you have to take care of manually is any AutoEntered data like serial numbers, especially so if any are used for related data. Depending upon the complexity of your solution this can be problematic and that's why you've had the alternative sorting methods suggested.

                       

                      Cheers,

                       

                      John

                      • 8. Re: how to INSERT (not add) a record in a portal?
                        timwhisenant

                        Hi Tony,

                         

                        To achieve what you request, create an index field (num) in the line items table and sort the portal on that value.

                         

                        See The “Portal Line Shift Demo” By Paul Spafford @ http://www.excelisys.com/filemaker-tips-tricks-demos-downloads.php for inspiration.

                         

                         

                         

                        Hope this helps,

                         

                        Tim

                        • 9. Re: how to INSERT (not add) a record in a portal?
                          debi

                          Tony,

                           

                          Lots of good advice already given, but I wanted to point out a few things:

                           

                          * Stefan's point is valid no matter how you choose to "insert" - you can choose the sort to occur at the relationship or at the portal.

                           

                          * If the sort order is non-numerica and non-alphanumeric, as you indicate, you could still sort on a value if you have a known set of values, by using a custom sort based on a hard-coded value list. Your rainbow color list would be a good example of how that would work. If you have a relatively stable sort order like that, it's what I'd recommend.

                           

                          * If you can go with the custom value list sort, you may still want to address secondary sorting - i.e., what if multiple values are green? Do you need to sort them further or is it OK if they're just lumped together?

                           

                          * Otherwise, you will probably need to create a numeric field and update it - per some of the suggestions above. Key to different approaches here is whether you are updating only the inserted value or all values following it as well. I like the idea of picking the row in front of the row to be inserted, grabbing that row's sort order value, and then using GetNth to grab the next row's sort order value. I think you could set the inserted row's sort order to the average of those two to prevent needing to update everything afterwards, though I haven't test this and I expect you could hit some limits at some point. FYI, the "+ .01" option could fail if you already had a .01 in the sequence.

                           

                          * Remember that these numerical sorts are all relative to each other only. That is, if you look at the child records outside of the portal for the parent, they would probably appear mixed up for your purposes. But if your goal is to really see the sort only in the context of the portal, that should not be a problem.

                           

                          HTH,

                           

                          Debi Rubel,
                          FullCity Consulting

                          • 10. Re: how to INSERT (not add) a record in a portal?
                            Stephen Huston

                            I particularly like Beverly's point about "swapping" the assigned sort numbers as you hit the Up/Down buttons, done via scripting. No need to worry about the practical limits of assigning decimal number increments and eventually hitting a limit. After all, the re-sorting option could be used a indefinite number of times on a single record over the course or the file's life. Swapping removes any practical limit on the sorting numbers.

                            • 11. Re: how to INSERT (not add) a record in a portal?
                              usbc

                              I like Bev's suggestion about "swapping" positions of records in a portal, too. However, the original request was to insert a additional new record. Big difference. With regards to the comments about adding a decimal, It is pretty simple to check for a duplicate and I'm sure someone knows how many decimal places the Filemaker engine can handle. So you could use .000000000001, etc. I use this method to describe points on patent drawings in a related table. My sort number begins as a Serial Number for each new record.  So while I might only have a few hundred points on a drawing, they are never created in the final order and there are many iterations over the life of a project.

                              I'm pretty sure I'll hit the practical limit of my life span first.

                              • 12. Re: how to INSERT (not add) a record in a portal?
                                DamianKelly

                                You can take the sort value from the previous and subsequent records sum then divide by 2

                                 

                                So:

                                value     sort

                                a          1

                                b          2

                                d          3

                                 

                                becomes

                                value     sort

                                a          1

                                b          2

                                c          2.5

                                d          3

                                • 13. Re: how to INSERT (not add) a record in a portal?
                                  tony.dhoop@telenet.be

                                  I chose debi's answer in setting the new record to  the average of the previous and the next, will solve any issue of ever running uinto a practical limit.

                                  I will introduce also the solution from Beverly, to move up & down ...

                                   

                                  thanks all!