11 Replies Latest reply on Jun 25, 2014 8:09 AM by philmodjunk

    Unexpected Case Statement result

    IsaacKnoflicek

      Title

      Unexpected Case Statement result

      Post

           I've got a very strange issue with case statements in a calculated field.  The field is supposed to check a boolean value (groups:isgenetics), if it's empty then return the first item in the Funding Strings value list, if it's 1 then return some static text.  Here's the statement:

           Case ( 
            
           IsEmpty(Groups::isGenetics);GetValue ( ValueListItems ( Get ( FileName ) ; "Funding Strings" ); 1 );
            
           Groups::isGenetics = 1; "Whatever";
            
           Self
            )
            
           For some reason when I use this it ALWAYS returns the first item of the funding strings value list.  That suggests an issue with the conditions, but if I replace the result of the first condition to something like this:
            
                Case ( 
                 
                IsEmpty(Groups::isGenetics);"Whenever";
                 
                Groups::isGenetics = 1; "Whatever";
                 
                Self
                 )

           Then it works correctly.  So somehow the calcuated result, the GetValue ( ValueListItems ( Get ( FileName ) ; "Funding Strings" ); 1 ), is screwing up the condition.

           It's not a positional thing either, I tried swapping the order and the result is the same.  Any ideas?

           Thanks,

           Isaac

        • 1. Re: Unexpected Case Statement result
          philmodjunk

               In what context are you using this calculation?

               In what table is the field with this expression defined?

               What are the relationship details linking the Groups table occurrence to an occurrence of the table where you defined this calculation?

               In a field of type calculation?

               or in an auto-entered calculation?

                

          • 2. Re: Unexpected Case Statement result
            IsaacKnoflicek

                 I'm using the Invoices starter solution.  isGenetics is a field I added on to the Companies table.  The calculated field is in the Invoice Data table, so it's being entered through a Portal on the Invoices.

                 The Value List is based on a table I created which is just a list of Funding Strings which each relate to a Company, and the company relates to the invoice which relates to the Invoice Data items.

                 So the value list is limited to the funds available to that company.

                 I'm using auto-entered calculation for the field.

                 THANKS!

                 Isaac

            • 3. Re: Unexpected Case Statement result
              philmodjunk
                   

                        I'm using the Invoices starter solution.

                   With what version of FileMaker? This starter solution is different with each released version of FileMaker.

                   

                        The calculated field is in the Invoice Data table,

                   But is it a field of type calculation or a field with an auto-entered calculation? If auto-entered, is the "do not replace.." check box selected or cleared?

                   

                         isGenetics is a field I added on to the Companies table.

                   There is no "companies" table in the Invoices starter solution released with FileMaker 13. Either you are using a different version, you added this table or this table actually has a different name.

                   The fact that isGentics is defined in one table, the calculation in a different table and the two are not directly linked to each other (both are linked to Invoice Data, is my current guess here) could be a factor in how this evaluates.

              • 4. Re: Unexpected Case Statement result
                IsaacKnoflicek

                     It's FM Pro 13.  I'm sorry it's been a while since I started customizing it so I'm hazy on what was stock and what isn't.

                     I created a new table called Companies and related it via name to the "Company" field of the User table so I could capture more information about the companies.

                     It is an auto-enter and Do Not Replace is set.

                     Thanks!

                     Isaac

                • 5. Re: Unexpected Case Statement result
                  philmodjunk

                       And how does the user table relate to the Invoice table? This chain of related records is getting longer and longer and thus more problematic.

                       You may want to upload a screen shot of Manage | Database | relationships with match fields visible.

                  • 6. Re: Unexpected Case Statement result
                    IsaacKnoflicek

                         I renamed a few fields in my example to (I thought) keep things simple, so sorry for the confusion.  Basically what was Company in the start solution is Group here, and it's probably worth noting the "Funding Strings" value list is based off the Projects table which does extend that chain of relationships, if that's the issue.

                         Thanks for your help!

                    • 7. Re: Unexpected Case Statement result
                      philmodjunk

                           Don't see a companies table, nor do I see a users table in this.sad

                      • 8. Re: Unexpected Case Statement result
                        IsaacKnoflicek

                             Companies = Groups

                             Users = Customers

                             Sorry for the confusion.

                        • 9. Re: Unexpected Case Statement result
                          philmodjunk

                               Groups----<Customers-----<Invoices------<InvoiceData

                               But then what did you mean by:

                               

                                    I created a new table called Companies and related it via name to the "Company" field of the User table so I could capture more information about the companies.

                               ???

                               I'm being careful here, because if there are more "boxes" (table occurrences) for these tables with the names of "company" and "user", they could be a factor in this issue.

                               But this is probably the key set of facts:

                               

                                    It is an auto-enter and Do Not Replace is set.

                               Key facts about auto-enter calculations that can produce issues:

                               1) If you add or change an auto-enter calculation, the data in this field in records created before this change will not automatically update. See: Updating values in auto-enter calc fields without using Replace Field Contents for a way to get such records updated.

                               2) even if you are not modifying the auto-enter settings, "do not replace existing value..." means that any subsequent change to a field referenced in the calculation will not produce a change in the value shown.

                               3) when an auto-enter calculation refers to data from a field in a related table occurrence, changes to the data in that field will not trigger a recalculation in the auto-enter field.

                               So items 1, 2, and 3 might produce discrepancies in what you expected to see. To rule them out, create a brand new Invoice Data record and see if you get the expected result.

                          • 10. Re: Unexpected Case Statement result
                            IsaacKnoflicek

                                 I was listing tables and fields from memory because I didn't think it was important, I'd forgotten that I renamed them.  I appreciate your patiences, and sorry again for the confusion.

                                 To test this I'm using the Invoice Layout, which has a portal where you add items to the invoice which is basically creating Invoice Data records.  So as I add items to the invoice I'm creating brand new invoice data records and that's where I'm seeing these strange results.

                                 As far as I can tell there's no reason why intermediate data should be getting put into that field where recalculating it might be an issue.  As soon as the item is added to the invoice this auto-enter calculated field checks if isGenetics is true or false and puts in the appropriate value.  This works as expected so long as the "appropriate value" is anything but "GetValue ( ValueListItems ( Get ( FileName ) ; "Funding Strings" ); 1 )"

                                 Unless you're suggesting that in calculating that result it's putting some intermediate data into the field that it cannot change later in the calculation?  If that were the case I'd expect the calculation to come out wrong, not to make the criteria wrong.

                                 My hunch was simply that there's too many semi colons and that result is somehow terminating the case statement and acting as the default, but that doesn't really make sense either.

                                 Thanks again for your help.

                                 Sincerely,

                                 Isaac

                            • 11. Re: Unexpected Case Statement result
                              philmodjunk

                                   I have produced far more complex case functions than this and they evaluated exactly as they were defined to evaluate so I don't think that's the issue and I've used the ValueListItems function extensively.

                                   What I was referring to by recalculation is that if the value of your isGenetics field changes, you won't see a change in existing InvoiceData records. This has been known to create confusion for others.

                                   I suggest the following test if you have not already done so: Put isGenetics on your invoices layout and see if it is empty or has a value at the time you add record in InvoiceData. Presumably, you create a new invoice, select a customer for the invoice and then start adding items to the portal to Invoice Data. If that is accurate, also check and see if it makes any difference in your results if you select the customer, click a blank area of your layout outside the portal and add your first line item vs. going straight from selecting a customer to adding line items. (The layout background click commits records and this can affect how your auto-enter calculation evaluates.)