1 2 Previous Next 23 Replies Latest reply on Aug 14, 2016 5:34 AM by GeertThijs

    Populate Value list with a calculated selection?

    tkemmere

      Dear all,

       

      I wonder: Can I populate a Value list with a calculated selection of a field in a table? And how?

       

      I have a table with companies. Each of them have a status. Like:

      • Supplier
      • Prospect
      • Customer type A
      • Customer type B
      • Competitor
      • Etc.

       

      Some prospects currently work with a competitor. For them I would like to set up a field "Works with..." and then a drop-down that only shows competitors, not suppliers, customers etc. so it can be easily selected. Hence the question, can I populate a Value list with a calculated selection of a field in a table?

       

      I have no need for a separate (related) table. In fact, I wouldn't want that cause competitors are also companies with all their respective fields, i.e. the same. But if that would be the only way to go, so be it.

       

      Is there someone willing to help me out on this?

      Thanks for the help. Thomas.

        • 1. Re: Populate Value list with a calculated selection?
          okramis

          Do a search on "Magic Value List" or have a look here:

           

          Magic Value Lists | FileMakerHacks

          • 2. Re: Populate Value list with a calculated selection?
            Mike_Mitchell

            The Magic Value Lists are an excellent option. Another thing you can do is use a relationship between a global field you set in scripting and the target table occurrence. (In your case, you would use a self-joining relationship.) This will allow you to use "related records only" in your value list, and FileMaker will build it for you based on the index.

             

            HTH


            Mike

            • 3. Re: Populate Value list with a calculated selection?
              tkemmere

              Thanks okramis,

               

              I thorroughly looked at the download from the page you referred to. My level is simply still too low to figure this out. I can see it work well, but I can only follow to some extent how this works, let alone reproduce it for my database.

               

              To give you an idea of my level, I wonder, what are TO's? What's an ERD? "MVL_MagicValueList" and "MVL_CheckBox1Feeder_cU" are those two new fields I should make? What are they for? Is magicValueList a separate newly to be created table? What does c1_cU and c1_c stand for? Are those also new fields I should make?

               

              As you can see, it's all spinning...

               

              To be continued

              • 4. Re: Populate Value list with a calculated selection?
                tkemmere

                Dear Mike,

                 

                Thanks for your reply too. I'm sure that's also a corerect answer. I just can't figure it out. Yet.

                I'll keep at it for a while and report back.

                 

                Thomas.

                • 5. Re: Populate Value list with a calculated selection?
                  Mike_Mitchell

                  A "TO" is a "table occurrence". The little boxes you see on your Relationships Graph aren't tables (although sometimes they're referred to that way); they are "occurrences" of tables. That's how you can have the same table on the Graph multiple times.

                   

                  An ERD is an "Entity-Relationship Diagram". It's a database documentation / planning tool that lays out what all the "entities" (things you track) are, and what the relationships should be between them. It's kinda sorta like the Relationships Graph, but not really.

                   

                  You can do some studying up on this by grabbing a copy of the FileMaker Training Series (available at Database Training and Database Tutorial | FileMaker). I highly recommend doing so; it'll give you a good basis for further design work.

                   

                  HTH


                  Mike

                  • 6. Re: Populate Value list with a calculated selection?
                    tkemmere

                    To continue,

                     

                    I rewrote what Mike advised as:

                    1. Create global field, set in scripting.

                    2. Create self-joining relationship between that global field and the target TO.

                    3. Make value list using "related records only".

                    4. FileMaker will fill the values based on the index.

                    5. Apply value list to the required dropdown.

                     

                    Ad 1...

                    I made a text field "Competitors" and I set it to global.

                    Now I need to write a script that fills this field with the names of all companies where Status = Competitor. I don't know how to do that. Sould the names end up comma-separated in there?

                    And when does the script trigger? I suppose OnObjectEnter.

                     

                    Ad 2...

                    I managed a second occurrence of the Companies table in the ERD.

                    I made a relationship from CompanyID to the global text field Competitors.

                    How do I make it "self joining"? Or does self joining simply mean that the relation is within one single table?

                    Does it matter which direction the relationship is setup?

                     

                    Ad 3...

                    I set the Value list to 'Use values from field' > Compettitors occurence of the Companies table::Competitors > Also display values from First/Main occurence of Companies table::CompanyName > Inclued only related values from First/Main occurence of Companies table > Show values only from second field > Sort English.

                    I proceeded anyway, after FM popped up that Competitors can't be indexed.

                     

                    Ad 4...

                    It didn't yet.

                    For now I typed "Company 1, company 2" in the global field Competitors. And also 2 index numbers of competing companies. I also tried semi-colons.

                     

                    Ad 5...

                    That's no problem.

                     

                    Obviously it is not working yet.

                     

                    Oh! Good news: When I enter one single index-number in the global field, that corresponding comany shows up in the dropdown! Great!

                     

                    I hope I'm on the right way. I would be greatful for some more help. I hope one day I can help out others.... Thanks in advance. Thomas.

                    • 7. Re: Populate Value list with a calculated selection?
                      tkemmere

                      @Mike thanks for your answer #5. I updated my last post with TO and ERD :-) And I will be looking into the FM training Tutorial. Thomas.

                      • 8. Re: Populate Value list with a calculated selection?
                        okramis

                        Here's a simple example file of mine:

                         

                        to reproduce do the following:

                        1 ) create a table "ValueList" with fields "_1" number, auto enter value 1; "Values" calculation text unstored = $$vlist

                        create one record in the new table.

                        MVL01.png

                        2) in the Relationships graph make a copy of the table occurrence "ValueList", name it "ValueList@unstored", connect the "_1" of "ValueList" with "_1" of "ValueList@unstored" with the connection type "x"

                        MVL02.png

                        3) In "File-->Manage-->Value Lists..." make a new value list, name it "MVL", use values from field-->Specify filed... --> use values from first field --> choose "Values" from "ValueList@unstored", check "Include only related values starting from:" select "ValueList"

                        MVL03.png

                        4) assign the value list "MVL" as valulist to your dropdown/popup-field

                        5) create a script, lets name it "SetVars" with just 1 step "Exit Script [ ]"

                        6) set a script trigger on the dropdown/popup-field "OnObjectEnter", choose the "SetVars" as the script and in the "optional script parameter" assign the values of your dropdown/popup-field in a Let()-statement to the variable $$vlist. You can set the values i.e. with the List() function, if it's static, or dynamically with ExecuteSQL()....

                        MVL04.png

                        • 10. Re: Populate Value list with a calculated selection?
                          Mike_Mitchell

                          tkemmere wrote:

                           

                          Ad 1...

                          I made a text field "Competitors" and I set it to global.

                          Now I need to write a script that fills this field with the names of all companies where Status = Competitor. I don't know how to do that. Sould the names end up comma-separated in there?

                          And when does the script trigger? I suppose OnObjectEnter.

                           

                           

                          Couple of ways to do this. Probably the easiest is to use ExecuteSQL ( ), like so:

                           

                          Set Variable [ $sqlQuery ; "SELECT company.name FROM company WHERE company.Status = 'Competitor'"

                          Set Variable [ $companyList ; ExecuteSQL ( $sqlQuery ; "" ; "¶" ) ]

                          Set Field [ tableName::Competitors ; $companyList ]

                           

                          The list of fields is a return delimited list. This will form what is referred to as a "multi-key". FileMaker will match any records in the child table that match any of the values in the global field. (Although I do suggest you use IDs instead of names; the possibility of a duplicate name does exist.)

                           

                           

                          Ad 2...

                          I managed a second occurrence of the Companies table in the ERD.

                          I made a relationship from CompanyID to the global text field Competitors.

                          How do I make it "self joining"? Or does self joining simply mean that the relation is within one single table?

                          Does it matter which direction the relationship is setup?

                           

                           

                          It's self-joining by virtue of the fact the two TOs refer back to the same table. But you need to have the global field on the parent side of the relationship (the TO where the layout you're working from is based). And yes, direction matters, because a relationship using a global field (or any other unindexable value) will only work one way - from the unindexed value to the indexed one.

                           

                           

                          Ad 3...

                          I set the Value list to 'Use values from field' > Compettitors occurence of the Companies table::Competitors > Also display values from First/Main occurence of Companies table::CompanyName > Inclued only related values from First/Main occurence of Companies table > Show values only from second field > Sort English.

                          I proceeded anyway, after FM popped up that Competitors can't be indexed.

                           

                           

                          That's why direction matters. See item 2.

                           

                          I hope I'm on the right way. I would be greatful for some more help. I hope one day I can help out others.... Thanks in advance. Thomas.

                           

                          You are, and you're welcome.  

                          • 11. Re: Populate Value list with a calculated selection?
                            tkemmere

                            Dear Okramis and Mike,

                             

                            First of all, I'm really impressed and grateful about how I'm being assisted by both of you! Thank you.

                             

                            I also tried the "Okramis solution". Unformtunately I can't figure it out. I get stuck at step 6. I'm using FM for 9 days now and have not before worked on Database solutions. Nothing comes sort of automatically yet at this stage... I openend your file Okramis, and I see it happening. I can see "color", "pet" and "fruit" in the Optional srcipt parameter, but I'm unable to find the colors, pets and fruits.

                             

                            I feel I'm closer to my objective with the "Mike_Mitchell solution". I'll persue that for now. The two of you are helping faster than I can answer! :-)

                             

                            This office is closing in an hour, and I wont be able to continue till Monday. But my response is due. Have a very nice weekend. Thomas.

                            • 12. Re: Populate Value list with a calculated selection?
                              tkemmere

                              Could it be that I need ProAdvanced? I have just Pro.

                               

                              In the Specify Calculation window, the term SELECT is not an available function. It'll only alow it with quotations around it, and it puts value: in front of it. So that is not the way to go.

                               

                              (Bedrijven means companies, bedrijfsnaam = companyname, ConcurrentenLijst = CompetitorsList).

                               

                              T

                              • 13. Re: Populate Value list with a calculated selection?
                                Mike_Mitchell

                                You're nearly right.   :-)

                                 

                                The SELECT isn't a function; it's a SQL command. All SQL commands are text strings in the ExecuteSQL function. When using SQL, you have to use a period (.) between the table name and the field, instead of the normal double colon (::). You also have to include the FROM clause so the parser knows what table to look at. So your SELECT statement looks like this:

                                 

                                     "SELECT Bedrijven.Bedrijfsnaam FROM Bedrijven WHERE Bedrijven.Status = 'Concurrent'"

                                 

                                Notice the quotes around the statement? That tells FileMaker it's a text string, not a command. So be sure to include them in your Set Variable step.

                                • 14. Re: Populate Value list with a calculated selection?
                                  tkemmere

                                  Back in the office. How I'm struggling with this. I read and re-read your response 5 times Mike, but I can't get it going. I'm sure once I've seen how its done, it'll be simple.

                                  This is also not the way, right? Both that added "SQL Text:"-bit as well as the errormessage tell me.

                                  Thanks for your patience. Thomas.

                                  1 2 Previous Next