9 Replies Latest reply on Dec 27, 2013 2:20 PM by philmodjunk

    How to Set a Variable   List ( Field::1 if Field::2 =1)

    shae1725

      Title

      How to Set a Variable   List ( Field::1 if Field::2 =1)

      Post

           I have searched the forum for a calculated List view and I thought I found the answer but cant get it to work!

           I want a list of    pkColorID    IF    ColorID_Count greater or eq 1    (or notEmpty if that would be a better way to do it?)

            

           Set Variable[$$ColorGroups;Value:List (If (MP_ColorGroupID_Portal::ColorID_Count  ≥ 1; MP_ColorGroupID_Portal::pkColorID))]

        • 1. Re: How to Set a Variable   List ( Field::1 if Field::2 =1)
          philmodjunk

               The list won't evaluate the way that you want. The if function will evaluate first and if the value of COlorID_Count from the first related record is greater than or equal to 1, you get a list of all pkCOloriIDs from the related record regardless of what value colorID_Count has for that related record. If the value is not greater then or equal to 1, you get no values in your liat.

               You'll need to define a calculation field with the If function in the related field and list the calcualion field or you should use ExecuteSQL instead of List.

          • 2. Re: How to Set a Variable   List ( Field::1 if Field::2 =1)
            shae1725

                 Hi Phil

                 That's what I thought but was experimenting to see if I could sort!

                 Not sure if I am ready to tackle ExecuteSQL yet. sounds complicated, Does it use the same functions as FM ?

                 How do you set a variable using it, Do you sort with ExecuteSQL first

                 I'm assuming I would use SQL calculated text?

                 Sorry for all the questions.

                  

            • 3. Re: How to Set a Variable   List ( Field::1 if Field::2 =1)
              philmodjunk

                   SQL is a "whole 'nother query language" for extracting data from FileMaker tables. If you don't already have a basic understanding of SQL queries as you might use them in other database systems, ExecuteSQL will be a challenging hill to climb--especially as any small syntax error produces the dreaded ? result with no feedback as to what specific part of the expression is the culprit.

                   You can find SQL SELECT query examples in the ODBC JDBC Guide that you can open from FileMaker Help. These SELECT Query examples can also be used in ExecuteSQL. SeedCode also offers their SQL Explorer tool to help build such queries. You can research SQL on line, but do so with care--the specific "flavor" of SQL used in any given database system will have differences unique to that system so not all general SQL examples that you can find on line will work with just any database system.

                   And keep in mind these key details:

                   1) The query itself is a text calculation. The expression must evaluate as text into a valid SQL query. All of the standard FileMaker text functions and operators might be used to construct this text though most often, you just use a quoted string of text for it.

                   2) The results are all contained in a single field. For your "selective list" that can be exactly what you want, but it can be a bit of a challenge if the results returned contain data from many fields of many different records--often such data would have to be displayed in a large formatted text field.

              • 4. Re: How to Set a Variable   List ( Field::1 if Field::2 =1)
                shae1725

                     Hi Phil

                     I downloaded the seedcode explorer tool and spent the day blindly trying to get some results!

                     Finally worked it out but was wondering if you could clarify some points for me?

                     I have added a snap where you will see I have set my $$ColorGroups with a calculation pasted from the seedcode layout!

                     My Script Works really well  - but what is the difference in setting a variable calculation as I have from using the function ExecuteSQL?

                      

                     Thanks

                      

                • 5. Re: How to Set a Variable   List ( Field::1 if Field::2 =1)
                  philmodjunk

                       but what is the difference in setting a variable calculation as I have from using the function ExecuteSQL?

                       I don't understand your question. You ARE setting a variable and you ARE using ExecuteSQL--all in the same script step.

                       So I'm not sure what comparison you are wanting me to make here.

                  • 6. Re: How to Set a Variable   List ( Field::1 if Field::2 =1)
                    shae1725

                         Only reason I asked was that I also set the variable using the ExecuteSQL function in a duplicate script  - and didn't use some of the steps in the calculation snap I shared. for example ReturnSub ="\n"

                          

                          

                          

                          

                          

                    • 7. Re: How to Set a Variable   List ( Field::1 if Field::2 =1)
                      philmodjunk

                           Ok, the SeedCode folks are allowing for every conceivable result a query might return. Since a return character could exist as part of the text in a text field, they are doing a very careful character substitution to keep any such return character from messing up the results. The added substitution code will not be needed if you are sure that the data returned by your SQL query will never include a return character from one of the referenced fields.

                      • 8. Re: How to Set a Variable   List ( Field::1 if Field::2 =1)
                        shae1725

                             OK

                             Im assuming it would be Best to use what SeedCode calculates until I get a better understanding of SQL.

                             From what I have seen a lot of table replications could be avoided using Excecute SQL the only downside I can see being if I change table or field names!

                             I have to say I have a feeling the SeedCode application is going to be an extremely valuable tool.

                             Many Thanks for your help :)

                        • 9. Re: How to Set a Variable   List ( Field::1 if Field::2 =1)
                          philmodjunk

                               If the fields you are querying are of type number, you shouldn't need the added code.

                               Getting table occurrence and field names locked up due to SQL queries is, in my opinion, the single largest limitation to using ExecuteSQL. It can indeed be used to drastically reduce the number of table occurrences in your relationship graph--though this also isn't always a good thing as it "buries" your data model design in possibly hundreds of different SQL queries.

                               It is theoretically possible to replace the quoted string used in a typical SQL query with a text calculation such that you are indirectly referring to table occurrence and field names so that name changes won't break the SQL. A custom function used with GetFieldName could suffice for that, but imagine what your calculation using SeedCode's format combined with such added details will look like...surprise