2 Replies Latest reply on Aug 5, 2014 7:05 AM by mcemond

    trying to auto enter form data based on selections

    mcemond

      Title

      trying to auto enter form data based on selections

      Post

           Hi

           I'm having a crazy time even trying to form the question so bear with me...

           I have one to many tables; State < Properties < Units < Tenants

           I have another table WorkOrders (I've tried relating it in a number of ways). Based on whatever info the user may have, I want them to be able to auto fill in the work order form with data from the other tables. So if all they have is a property name, the work order gets related info from the Properties and states tables filled in. I've looked into simple pop up lists for each table but I don't want them to be able to enter invalid combinations or have to look through 6000 tenants etc. I've tried conditional value lists but they didn't cooperate with me very well when incorporating tables above the parent level. I've tried button scripts but apparently copy and paste rips out the relationship info. I've tried field calculations but entering all the possible outcomes was too tedious. I've also gone through all the related Lynda.com videos for FM 12 and 13 and nothing popped out at me.

           How does one usually go about solving this type of issue with FM (Pro 13)? This must be a pretty common use I'd think.

           Thanks
           Mike

            

        • 1. Re: trying to auto enter form data based on selections
          philmodjunk

               Many of those options that you report not working should work for you. Chances are that some detail in what you wanted to do was not set up correctly.

               and while I would not use copy/paste for this type of action, "apparently copy and paste rips out the relationship info." makes no sense here.

               It would appear that you need a hierarchical conditional value list where each value selected in the preceding conditional value list filters the values listed in the next value list.

               But there are also aspects of what you want to do that are not clear to me.

               

                    So if all they have is a property name, the work order gets related info from the Properties and states tables filled in.

               In this example, does that mean then that the work order would show no data from the units and tentants table? Such as a work order to re-pave the parking lot and thus is a work order that is not specific to a particular unit or tenant?

               Some Links on conditional value lists--including one on Hierarchical conditional value lists:

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

               The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

               And you may find that you need to create additional Tutorial: What are Table Occurrences? in your relationship graph in order to get the conditional value lists to work correctly.

               And here are some demo files that show more advanced methods of searching out data in a FileMaker database that may serve as useful examples:

               An autocomplete enabled drop down list of names that links records by ID number:

               FileMaker 12 or newer users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
               Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

               Other enhanced search methods:

          https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

               If you are using FileMaker 12 or newer, open this last file from FileMaker's File menu to get a copy converted to the newer file format.

          • 2. Re: trying to auto enter form data based on selections
            mcemond

                 Wow. Thanks so much for all this info. I'll be going through it all today and will certainly post follow up questions. You are correct about the paving example where units and tenants info would be bypassed. 

                 Thanks again!
                 Mike