12 Replies Latest reply on Jul 29, 2014 9:30 AM by Matty_1

    Assigning coded locations in order ...



      Assigning coded locations in order ...


           I have a set of records with a portal.  Within the portal I have a list of locations a person is required to go for this one particular situation.  The locations can be any number of stops and I'm wondering if it possible to have a script or better yet a  formula that can take the drop code and place it in field in the parent record in order of how it's been entered in the portal i.e.: AAA/BBB/CCC/DDD/EEE etc.


           If CCC get changed to CbC the change occurs in the middle as it should.  Am I shooting for the stars on this one?

        • 1. Re: Assigning coded locations in order ...

               A calculation field with:

               Substitute ( PortalTable::PortalField ; ¶ ; "/" )

               Will concatenate the value of PortalField for all related records in the portal with a / separating each value. This assumes that your portal is unfiltered as list ignores any portal filtering that might be reducing what records appear in the portal.

          • 2. Re: Assigning coded locations in order ...

                 WOW, WAY simpler then I would have imagined.  Thank you very much!  I had to make a small modification to achieve my goal.  I added List to have them all included back to back and this is what it looks like:

            Substitute ( List ( PortalTable::PortalField ) ; ¶ ; "/" )


            With this being so easy, would it be possible to filter out sequential repeats i.e.: AAA/AAA/BBB would show up as AAA/BBB however AAA/BBB/AAA would stay unchanged because the BBB/ broke the sequence.

            • 3. Re: Assigning coded locations in order ...

              Substitute ( List ( PortalTable::PortalField ) ; ¶ ; "/" )

              Was what I had in mind. Good job reading my intent from an incorrect post. wink

              That would not be nearly so easy to do. It's simple to filter out all duplicates (and this also changes the order shown) but filtering out only those duplicates that are adjacent would not be nearly so simple. A custom function defined in FileMaker Advanced becomes your best option for that. Without the custom function, you are looking at a looping script which then has to be consistently performed every time a value is added/deleted/changed.

              • 4. Re: Assigning coded locations in order ...

                     Thank you Phil,  How about simply filtering out duplicates, is there an easy to do that?

                • 5. Re: Assigning coded locations in order ...

                       But this also changes the order in which the values are listed.

                       You can define a conditional value list based on the portal's relationship to list all values from the portal with all duplicates omitted. But the values will be sorted in ascending order as well. ValueListItems would then be used in place of List.

                  • 6. Re: Assigning coded locations in order ...

                         ExecuteSQL, on the other hand can use / as the record separator, use the DISTINCT keyword to omit duplicates and use either WHERE or JOIN to get the list from the related records shown in the portal.

                         This requires FileMaker 12 or newer to produce and a working knowledge of SQL.

                    • 7. Re: Assigning coded locations in order ...

                           I am using this custom function however it is always adding a "/" at the end of the results.  Do you see how I could modify the function to remove that?  For the record the custom function is called RemoveDuplicates.


                           Let ( [
                           TheValue = LeftValues ( Text ; 1 ) ;
                           TheCount = ValueCount ( Text ) ;
                           NewText = Replace ( Substitute ( "¶" & Text & "¶" ; [ "¶" ; "¶|" ] ; [ "|" & TheValue ; "" ] ; [ "|" ; "" ] ) ; 1 ; 1 ; "" )
                           ] ;
                                Case ( TheCount > 0 ;
                                     TheValue & RemoveDuplicates ( Replace ( NewText ; Length ( NewText ) ; 1 ; "" ) )
                           Here is how I have it integrated into my calculation field:
                           Substitute (  RemoveDuplicates ( List ( DispatchFinancials_DispatchContracts::ULDispatchName ) ) ; ¶ ; "/" )
                      • 8. Re: Assigning coded locations in order ...

                             Let ( string = Substitute (  RemoveDuplicates ( List ( DispatchFinancials_DispatchContracts::ULDispatchName ) ) ; ¶ ; "/" ) ;

                                     Left ( String ; Length(string) - 1 )

                        • 9. Re: Assigning coded locations in order ...
                           Thank you.
                          • 10. Re: Assigning coded locations in order ...

                                 One last question on this.  On very rare occasions I will need to change the natural location order that being calculated based on which portal record is ahead of which but I can't simply change the portal order I need to implement a numbering system in this case.  I have done this and set the relationship to sort based on that numbering system (when applicable of course).  The problem is that it does not refresh the order as soon as I enter a numbering order.  I need to change the location and set it back to the original for it to refresh.  I've double checked to see if there was some weird indexing going on and there isn't (not that you could ever index a a calculation calling on a record in another table)  and I've also tried refresh/flush cache and still I have the same issue.

                            • 11. Re: Assigning coded locations in order ...

                                   When a layout fails to update as needed:

                                   1) commit the record. As a quick test, click a blank area of the layout outside of the portal and see if that changes what you see. Commit records is a script step so sometimes you can force the commit via a script trigger.

                                   2) Refresh the object (fileMaker 13) Give the object that's not updating an object name and use Refresh object in a script to refresh the object.

                                   3) Refresh window. another script step but more drastic than refresh object. It can cause a bit of "layout flash".

                                   4) Refresh window [Flush cached join results] This is a "wipe the slate clean and start all over just as though you accessed this layout for the first time" step. It can lead to major delays in updating in some situations--especially with iOS clients of a hosted database where you have both a slower connection speed and slower processors than a full up computer. With large data sets involved such as from a summary field, this can bring your system to a slow crawl. I thus may try this as a test to see if it helps but then will try other methods such as tricks with a cartesian join to avoid using it.

                                   5) Change modes twice. Have your script change to preview mode and then back to browse mode. I rarely do this, but if all else fails...

                              • 12. Re: Assigning coded locations in order ...

                                     I had already tried steps 1,3 and 4 but wasn't aware of step 2.  This did not do the trick either so had to resort to 5 :(