9 Replies Latest reply on Sep 21, 2016 9:55 PM by padje

    variable listbox


      Hello Filemaker developers, I’m writing an application to support some contactmanagement, invoices, sales orders,  inventory, …


      The application will be used by different people on different locations, on different devices.


      A (in the meanwhile) good friend of mine offers his help developing my application.  He is not familiar with Filemaker but he is an authority in data modelling, requirements analysis, business integration, modular design, …


      He is the Jedi, I’m the padawan.


      On his advice I’m refactoring/optimising (pieces of) the application for several reasons.  One of them is to avoid unnecessary data access  as much as possible.  (reduce to the minimum).  Too bad I’m not a Filemaker guru who can implement his wise words in a breeze.


      At this point we are looking for a solution to refactor a small piece of logic for a simple frontend concern.  We are  wondering if it is possible to implement this functionality without even accessing the database.


      In this case we need to manage the correct state of a sales order.

      Different people will do a follow up of the sales order so it is very important to always have the correct state of the order.


      Some states must be set automatically, others must be set manually.

      We need to avoid errors in the manual manipulations of the states.


      Only valid states must be available depending on the current state of the order.

      Take a look at the attachment : order state diagram


      I want to change my current code (magic valueslist/conditional valuelist – linked to a table) into  a piece of logic,  whereby the set of valid states the user can choose from,  is calculated without accessing my database.


      This is the logic I’m trying to implement on my order screen :


      1. 1. display order information
      2. 2. calculate the valid new order states based on the current state value
      3. 3. fill a listbox with the calculated order states
      4. 4. select a new value from order states
      5. 5. save the order/update new state


      I’m want to implement step 2 and 3 without accessing my database.


      Hereby the logic of my state calculation:


      • Case order_state = code_Init

        • Set StateList (1)= (code_Ordered, ‘Ordered’)

        • Set StateList (2)= (code_Refused, ‘Refused’)

      • Case order_state = code_Ordered

        • Set StateList (0)= (code_Ordered, ‘Ordered’)

        • Set StateList (1)= (code_Delivered, ‘Delivered’)

      • Case order_state = code_Delivered

        • Set StateList (0)= (code_Delivered, ‘Delivered’)

        • Set StateList (1)= (code_Checked, ‘Checked’)

        • Set StateList (2)= (code_Incomplete, ‘Incomplete’)

        • Set StateList (3)= (code_Damaged, ‘Damaged’)

      • Case order_state = …

      • Case …


      Some notes :


      All my state codes (code_Deliverd, code_Checked, …) are stored in a systemcode table.

      These codes won’t change very often, so they can be used hard coded.


      I’m trying to populate my listbox, using a 2-dimensional array ( statelist[ ][ ] ), rather than accessing the database.


      To be clear, I’m aware of workarounds like the magical valuelist trick




      but I was hoping for a more elegant (hardcoded) solution for populating and displaying the valid states.


      Thanks for taking the time reading this, and thanks for your input !  Mucho appreciated !

        • 1. Re: variable listbox

          What is your front-end interface? Is this some sort of web publishing situation where you're trying to use FileMaker as the back end?


          What is the actual problem you're trying to solve?

          • 2. Re: variable listbox

            Hey Mike


            It's a filemaker only application, no web publishing.  We do foresee a multi-client use in the future.


            The actual problem is :  We need a variable valuelist, depending on a current status of a record, to change a status of a record.


            We don't want to query the database (systemcode table) each time we change a status. We prefer having some kind of hardcoded list which get loaded on startup for example. 


            Depending on the status of the current record, the valuelist displays only the possibe states.


            I hope that is more clear.

            • 3. Re: variable listbox

              You're getting advice from someone who thinks FileMaker is a front-end / back-end configuration. It's not. The value lists in FileMaker are indexed and very fast. They automatically populate when you cache the file at startup. Your friend's advice is probably coming from your use of the Magic Value List technique, which involves (in many cases) SQL statements - and is likely unnecessary.


              You don't "query the database" when you build a value list. When you change a value in a field, FileMaker will automatically update the index for that field - which is where the value list comes from. There is no need or benefit in attempting what you're describing.


              I would use a standard value list instead of attempting the Magic Value List, unless there's a weird configuration that doesn't allow you to do that. Your value lists can be populated easily either through standard indexing, or just a script that updates the appropriate field using a Script Trigger.

              • 4. Re: variable listbox

                padje wrote:


                calculated without accessing my database.


                You mention this a few times throughout your post, and I agree with Mike that this is likely the crux of your mental block.


                While you do have to worry a little bit about minimizing the calls that the client has to make to the database backend, if you want to make things dynamic as you are you can not completely remove it.

                • 5. Re: variable listbox

                  A simple custom conditional value list can do what you want, no ExecuteSQL required.


                  A "list box", where the values are all listed in a text box and the user clicks a row to select that value is possible in FileMaker, but seems an unnecessary complication given your current level of expertise.

                  • 6. Re: variable listbox
                    David Moyer


                    you might want to look through the following thread - focusing on the latest post from the OP.

                    User Modified Value Lists

                    1 of 1 people found this helpful
                    • 7. Re: variable listbox

                      Thank you for all the responses.  Thank you also for confirming the fact that FiIemaker in itself doesn't operate as a programming language with front-back separation possibilities.


                      philmodjunk although my level of expertise is limited, could it be possible to point me in the right direction of the "list in a textbox" approach ?  I'm eager to learn and I'm willing to tackle this one too.  Maybe a little bit of code or the link to an existing thread can clarify some stuff.


                      moyerdave  Thanks for the tip.  I will check this thread for sure.


                      Have a nice evening !

                      • 8. Re: variable listbox

                        I have a demo file of many enhanced value selection techniques where a clickable List box is one of the examples. Each comes with detailed documentation and since it's in a working file you can also tear open the file to see exactly how each example is set up that way as well.


                        Adventures in FileMaking #2 - Enhanced Value Selection


                        You'll find a field of cilckable hyperlinks for additional resources that includes a link to Adventures in FileMaking #1 which has a set of Conditional Value List examples that you might also find useful.

                        1 of 1 people found this helpful
                        • 9. Re: variable listbox

                          Beyond my expectation !  Thank you.