1 2 Previous Next 16 Replies Latest reply on Jul 25, 2014 4:25 PM by philmodjunk

    Records filed or in use

    tays01s

      Title

      Records filed or in use

      Post

           I'm sure this may be a simple problem but I can't quite get how to make it work. I have a 'name' field. Currently the same table and layout have a:

           NameStatus = Use or File values

           NameStatusList = In use or Filed.

           I would like to be able to specify what the pop-up list is populated with using a radio-button = In use or Filed. However, once I've finished working with a Name record I'd like to specify its NameStatus = Use or File. So for example a Name may appear when I've specified 'In use', but I may decide to change it to filed. Next time the pop-up showing names is activated that particular name should disappear from the pop-up list if that list is still set to 'In use'. I haven't figured how to avoid a circular argument.

        • 1. Re: Records filed or in use
          philmodjunk

               It's not at all clear what you are trying to do.

               It appears that you need some conditional value lists where the values that show in the list are controlled by values you set in different fields of your database. Is this what you are trying to do? Do you know how to set up a conditional value list?

               It may help to offer an example that walks us through what the user is going to do via data entry and how you want the database to respond to that data entered.

          • 2. Re: Records filed or in use
            tays01s

                 Yes I need a conditional value list and in general terms can do this.

                  

                 Eg. I have a Names table and layout based on it with a pop-up listing the names (value list: NamesID::Name) with a script attached to the pop-up including Global::gSelectNameID to set the name. The user will select either 'In use' or 'filed' to populate the pop-up with either current or filed names. Once the user has finished with a record they may change the status of the record by clicking 'Use' or 'File'. I want these to change which list the record will be displayed in on the pop-up to: 'In use' or 'Filed', respectively.

                 All the fields mentioned above are currently on the Names table and layout. I'm assuming I really need to split these between 2 tables together with TOs?

            • 3. Re: Records filed or in use
              philmodjunk
                   

                        with a script attached to the pop-up including Global::gSelectNameID to set the name.

                   "Setting the name" shouldn't need a script. What am I missing there?

                   A "big picture" explanation would help here.

                   

                        I'm assuming I really need to split these between 2 tables together with TOs?

                   I can't tell from what has been posted thus far. you might use 1 table with two TOs of the same table or there might be a need to have completely separate tables.

                   But it appears that you have matching values specified here that don't actually match. A relationship based conditional value list might match a value of "Filed" to all related records where a match field stores the value "Filed" and matches "In Use" to "In Use". But you seem to want to match "File" to "Filed" and "In Use" to "Use". That won't work, but you can add some calculation fields such as:

                   case ( YourTable::Field = "File" ; "Filed" ;
                             YourTable::Field = "Use" ; "In Use"
                           )

                   And now this calculation field can be a match field in the relationship to match values correctly.

              • 4. Re: Records filed or in use
                tays01s

                     I've used such a calc on my Name::NameList field. So if I change NameStatus to 'use' or 'file' the NameList changes to 'In use' or 'Filed'. The Name field is Name_2::ID showing Name_2::Name.

                     What I'm not sure about it how to relate Name to its TO Name_2.

                • 5. Re: Records filed or in use
                  philmodjunk

                       Name::CalculationField = Name 2::FiledInUseField

                       This should allow you to edit the third field to be either "File" or "Use" and then the relationship matches to records that are either "filed" or "in Use".

                  • 6. Re: Records filed or in use
                    tays01s

                         So if I have:

                         1. a layout based on Name and a TO Name2

                         2. NameStatus 'use', 'file' changes the calculated field NameStatusList to 'In-use', 'Filed'

                         3. Relationship between TOs: Name::NameStatusList < Name2::Name

                         4. Pop-up based on field?

                         5. Value list based on?

                    • 7. Re: Records filed or in use
                      philmodjunk

                           Relationship between TOs: Name::NameStatusList = Name2::Name

                           Use =, not <. But "Name" seems the wrong field here for Name2::Name. you want the field with the values "In Use" or "Filed" not a person's name here.

                           

                                Value list based on?

                           Open Manage | Value Lists and create a new value list, selecting the "use values from a field" option.

                           Select your fields from the Name2 table occurrence, not Name.

                           Click the "include only values starting from" check box and select Name, not name2 as the "starting from" table occurrence.

                      • 8. Re: Records filed or in use
                        tays01s

                             Sorry, Name::NameStatusList = Name2::Name is what I meant.

                             Is value list: Name2::NameID and second field to display: Name2::Name?

                             And the field on the layout a pop-up based on? I tried Name::Name and it doesn't work.

                        • 9. Re: Records filed or in use
                          philmodjunk

                               Is value list: Name2::NameID and second field to display: Name2::Name?

                               If you want to show the name, but enter the ID, yes. It all depends on what you want this value list to do.

                               With NameID as field 1 and Name as field 2, you'd format the NameID field with this value list not name as you are actually entering an ID, even if you use the popup menu option and hide the first field value in the value list set up.

                          • 10. Re: Records filed or in use
                            tays01s

                                 Yes, I want a list of names displayed by the pop-up.

                                 Re. your last paragraph, what field then should I be using on the Name layout to which I attach the value list?

                                 And, if I wanted a 2nd 'filter' can I just add another criteria still using the Name2 TO?

                            • 11. Re: Records filed or in use
                              philmodjunk
                                   

                                        what field then should I be using on the Name layout to which I attach the value list?

                                   With NameID as field 1 and Name as field 2 (specified for your value list), you'd format the NameID field with this value list not name as you are actually entering an ID,

                                   

                                        And, if I wanted a 2nd 'filter' can I just add another criteria still using the Name2 TO?

                                   If by that, you mean can you add another pair of match fields to the existing relationship to further "filter down" the number of values, yes.

                                   Here are some links on 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.

                              • 12. Re: Records filed or in use
                                tays01s

                                     It might be easier if I upload a file to DropBox or similar. I've stripped out all except the Questionable parts but the file size has not skrunk; still 3.3Mb Is there a way to reduce size?

                                • 13. Re: Records filed or in use
                                  tays01s

                                       I've uploaded a file to: https://www.dropbox.com/s/lws2o0g4bj18l97/Names.fmp12

                                       The intent is for a user to select whether a Name is 'In-use or Filed' and what 'Location' the Name is at from the pop-up 'Name'.

                                       The user may then change the NameStatus or Unit (Location).

                                       I've failed to get the system working properly!

                                  • 14. Re: Records filed or in use
                                    philmodjunk

                                         Did you select the NameID field and format it with a drop down list or popup menu using the value list?

                                         What values do you see appearing in the value list?

                                         If you put a portal to Name2 on your layout and specify the same NameID and Name fields, do you see the values you want to see in your value list?

                                    1 2 Previous Next