10 Replies Latest reply on Jul 27, 2014 4:18 PM by synergy46

    FM 13 adv ... dynamic value list...

    synergy46

      This is a pretty simple 2 table, 1 relationship problem.

      Relationship333.png

       

      and there are only 2 layouts: Transactions and Accounts:

       

      Here is the transaction layout:

      transactions333.png

       

      and here is the Accounts layout:

      Accounts333.png

       

      Each account is either a Expense or a Revenue account. On the Transactions layout,

      the user enters (in the yellow field above): Check (Expense), Deposit (Revenue), Bank Fee (Expense) or

      Dr Card (Expense).

       

      Since each Account Description is either an Expense or Revenue account, I need a way to

      limit the Account Description dropdown to either Expense or Revenue accounts as dictated by

      the users entry as: Check, Deposit, Bank Fee or Dr Card ... (Expense, Revenue, Expense, or Expense)

       

      I "get" dynamic value lists but can't seem to grasp how this might come together.

       

      Thanks for reading...

       

      R

        • 1. Re: FM 13 adv ... dynamic value list...
          BruceHerbach

          You could create a value table.  Each record would have two fields,  one would be type and the other a value you want in the drop down.  The type field would have two possible value,  Expense and Revenue.   Connect the value table to the accounts by the type field.  Then create your list using value based on the relationship.

           

          HTH

          • 2. Re: FM 13 adv ... dynamic value list...
            synergy46

            It seems like the proposed 'value' table is the same as the existing

            'ACCOUNTS' table?

             

            And, what is the mechanism for determining when a user selects, CHECK, for

            example, that selection gets converted into "Expense" and then just expense

            ACCOUNTS are shown?

             

            Thanks

             

            Ron

             

             

            On Sat, Jul 26, 2014 at 10:38 AM, Bruce Herbach <noreply@filemaker.com

            • 3. Re: FM 13 adv ... dynamic value list...
              erolst

              synergy46 wrote:

               

              It seems like the proposed 'value' table is the same as the existing

              'ACCOUNTS' table?

               

              What Bruce is describing is an additional lookup table table.

               

              IIUC, you can do without a value list by reversing the workflow: let the user select the TransactionType (i.e. the more specialized datum), then auto-fill the account type (the more generic type) via the relationship Transaction::transactionType = Lookuptable::transactionType

               

              i.e. your table has the fields transactionType, and acountType, and 4 records with

               

              Check, Expense

              Deposit, Revenue

              Bank Fee, Expense

              Dr Card, Expense

              • 4. Re: FM 13 adv ... dynamic value list...
                synergy46

                I think I am not being clear.  (or, I am not clearly understanding your 'fix'...)

                I do not need to 'do away with the lookup', I need to restrict the accounts that

                appear in the lookup to the accountType that corresponds to the Type selected

                in the first value list.

                 

                I have replaced the 'static' value list with this NewTYPES table:

                TypesTable22.png

                 

                For example:

                The user selects from Types:Type 'Checking' .  This puts the value 'Checking' into the Transaction::Type field. 

                 

                Transaction22.png

                 

                Since Checking has an ExpRev field value of "Expense", and since each of the

                Accounts ...

                Accounts22.png

                 

                ...has a ExpenseRevenue field that is either "Expense" or "Revenue", I need to restrict

                the Account::Descriptions dropdown to, in this case, just the Accounts that have an

                Account::ExpenseRevenue value of "Expense".  (That would be, Miscellaneous Exp, Lodge Rent,

                and Bank Charges.

                 

                Thanks for reading ...

                • 5. Re: FM 13 adv ... dynamic value list...
                  synergy46

                  I got it to work ... except for this anomoly...

                   

                  First I added a NEWTYPE table: 

                   

                  Then I established this relationship:

                   

                  newtype111.png

                   

                  But, as you can see, I only get 6 of 7 expenses to show and 3 or 4 revenue items to show.

                  Dropdown11.png

                  I am baffled.  What is causing this strangeness?  (I have tried refreshing and comitting

                  records... no help...)

                  • 6. Re: FM 13 adv ... dynamic value list...
                    synergy46

                    Update:  The 'cause' of the limited listing was that FM was apparently only looking at the Description column in "current records".  Close but no cigar.

                     

                    So, I then attached some code to the Types::Type field (check, deposit etc) that picks up the Expense or Revenue field and then does a find on the Accounts table.   This works great. 

                     

                    At this point I have in the Accounts table just the Expense records (for example).  I then go into FM and setup my dropdown but it still shows ALL records...

                     

                    Am I doing something wrong or shouldn't FM show the current found set for a dropdown definition?

                    • 7. Re: FM 13 adv ... dynamic value list...
                      erolst

                      synergy46 wrote:

                      I think I am not being clear.

                       

                      I think everyone (myself included) thought that you wanted to look up account types, when you actually want to look up the accounts themselves.

                       

                      Well, then 1. in fact you don't need that additional table, and 2. since it seems you'll already have the relationship

                       

                      Transactions::type -- Types::type

                       

                      you can add to that

                       

                      Types::expRev -- Accounts_forPopup::expRev

                       

                      and create your value list with Accounts_forPopup::Description, starting at Transactions.

                      • 8. Re: FM 13 adv ... dynamic value list...
                        synergy46

                        I can't get that to work.  So, here is the .fmp12 file .  (My app is freeware so if you can get something out of what I have done... go for it...)

                         

                        https://dl.dropboxusercontent.com/u/6775417/CheckbookRegBeta72DropdownPosted.fmp12

                         

                        What I am trying to do is:

                        a)  Enter a value in the 'yellow' 'Deposit or Check' Type dropdown:  check, deposit etc...

                        b) Then show the corresponding "Expense" or "Revenue" accounts in the "TestDropdown" box.

                         

                        Thanks for your help.

                        • 9. Re: FM 13 adv ... dynamic value list...
                          erolst

                          synergy46 wrote:

                           

                          I can't get that to work.

                           

                          Because you didn't follow the instructions in my post. In your file, the field(s) for the value list must be selected from Accounts 2, and you must check “Include only related values starting from” Transactions.

                          • 10. Re: FM 13 adv ... dynamic value list...
                            synergy46

                            It works!

                            Thank you for your guidance.

                             

                            Now I am going to 'go to school' on "how" it works.

                             

                            Ron