AnsweredAssumed Answered

Populate repeating field from list of values (to create an alphabetic button bar)

Question asked by justinc on Jun 27, 2017
Latest reply on Jul 1, 2017 by justinc

Hi all,

I'm having some issues figuring out how to get an Auto-enter calculation to populate individual repetitions of a repeating field, from a list of values.


My ultimate goal here is to create an alphabetic-button array, of variable length/content, that runs down beside a portal.  These will become buttons used to fire a script that would scroll the portal to the first matching record of that letter.  (This is to mimic the button array on many Contacts or Address apps on a phone, etc.)  The 'variable' part comes into play because I would like the buttons to only show up if there is an entry for that letter.  I don't want it to be a constant 26 letters long.


E.g. If I have related records with "Jones", "Smith", "Snyder", "Greene" I would like my list of buttons to be "G", "J", "S".  (Vertically oriented.)


The table relationship is fairly simple:  a global field is set by a drop-down + value list, setting the global field to an ID number, which controls a relationship to the related table:   TableA::ListID_g => TableB::ListID_fk


I'm not having any troubles generating the list of unique/distinct values from the related records.  I have an auto-enter global field (some parts edited or removed for clarity):


TableA::AlphaList_g =

Let (

    ~a = ColumnID_g        //trigger field

    ; If ( IsEmpty ( ~a )

         ; ""

         ; FilterValues (

             List ( "A";"B";"C";"D"; <...etc...> ;"X";"Y";"Z")

             ; ExecuteSQL (

                "SELECT LEFT ( name_last, 1) FROM TableB WHERE ListID_fk = ? "

            ; "" ; "" ; TableA::ListID_g

           )    //END Esql()

        )    //END FilterValues()

    )    //END If()

)   //END Let()


I have another version of that calculation which just uses ESQL to do the 'Distinct' and sorting parts...but I kind of like using internal FM functions to avoid SQL slowness.  (Not that I have seen any slowness with the fully-SQL version at the moment.)


Then I have another AE global Repeating field that attempts to split the list of values into individual repetitions.  These repetitions are then what's used on the layout itself to become the buttons.  I cobbled this together and found other suggestions that recommended this same technique:


TableA::AlphaButtons_rg [26] =

GetValue ( AlphaList_g ; Get(CalculationRepetitionNumber) )


Getting a repeating field to populate is discussed in this other thread - but I couldn't get those answers to work in my case.  I have tried defining the repeating field to directly create/collect the list of values, and thus skip the need for the other field; but that didn't work any better.  Maybe it will if I get the rest of it worked out.


(Why a repeating field?  Well, I didn't want to put 26 buttons on the layout with individual hide conditions - that wouldn't slide-up/compress to remove empty spaces.  A button bar might have worked except that the buttons that were visible would extend to fill the specified space - I wish there was an option for keeping each button the same size, regardless of how many were being shown, and have them slide together to remove blank spaces.)


So, how can I get the repeating field to correctly populate, based on a list of values?

Does anyone have other suggestions on making an alphabetic button-bar-style widget?