1 2 Previous Next 22 Replies Latest reply on May 6, 2015 2:37 AM by gavjuan

    Case Function

    gavjuan

      Title

      Case Function

      Post

      I have an invoice layout that needs to be sent in different currencies depending on the account the money is being paid into. I want to select the relevant bank details according to the currency displayed prefixing the invoice total. I have a field with a dropdown list of currency symbols "£", "$", "€" etc. The Bank Details field has the Calculation below. It works except I want it default to Details Sterling for anyone without an alternative currency account.

      Case  (Currency = "$"; Photographers Details::Details Dollar ; Currency = "€"; Photographers Details::Details Euros ; Currency = "£"; Photographers Details::Details Sterling ; Photographers Details::Details Sterling )

        • 1. Re: Case Function
          Timothy Bentley

          Try unchecking "Do not evaluate if all referenced fields are empty".

          • 2. Re: Case Function
            gavjuan

            I did try that but it returns 'nothing' rather than the default Sterling

            • 3. Re: Case Function
              philmodjunk

              If all referenced fields are empty, an empty result is the only possible outcome (default sterling would also be empty) so clearing that check box will not change the results for this calculation.

              Case  (Currency = "$"; Photographers Details::Details Dollar ;
                         Currency = "€"; Photographers Details::Details Euros ;
                         Currency = "£"; Photographers Details::Details Sterling ;
                         Photographers Details::Details Sterling )

              For any value other than the three currency characters listed, this calculation will return the value of Photographers Details::Details Sterling.

              But what value is returned at that point in time can depend on whether this is defined in a calculation field or as an auto-entered calculation and what record in Photographers Details is currently liked via relationship to the table and record where this expression is evaluated. (At least the Table::field syntax used implies that Photographers Details is the name of a related table occurrence.)

              • 4. Re: Case Function
                gavjuan

                They are linked tables - (Invoices + Photographers Details) using the Photographers intials as the link. The calcualtion below is where I started and should work? I need - If "$" is empty it should return the Sterling value which is present in every record - but it doesn't.

                Case  (Currency = "$"; Photographers Details::Details Dollar ;
                           Currency = "€"; Photographers Details::Details Euros ; Photographers Details::Details Sterling )

                I've tried it as a calcualtion field and auto-entered but still same result - were you suggesting one of these should work?

                • 5. Re: Case Function
                  gavjuan

                  They are linked tables - (Invoices + Photographers Details) using the Photographers intials as the link. The calcualtion below is where I started and should work? I need - If "$" is empty it should return the Sterling value which is present in every record - but it doesn't.

                  Case  (Currency = "$"; Photographers Details::Details Dollar ;
                             Currency = "€"; Photographers Details::Details Euros ; Photographers Details::Details Sterling )

                  I've tried it as a calcualtion field and auto-entered but still same result - were you suggesting one of these should work?

                  • 6. Re: Case Function
                    philmodjunk

                    I'm seeing an increasing number of duplicate posts here. Did you have trouble posting and post a second time or did all seem to work normally at your end but we got duplicates anyway? (I'd like to pass on a report of this issue to ModMan--the person at FileMaker Inc. responsible for this forum.)

                    How are you implementing this calculation? In an auto-entered calculation, a calculation field or a script step?

                    What is the context of that calculation? invoices?

                    What data data type is the currency field?

                    • 7. Re: Case Function
                      gavjuan

                      Apologies for duplicate posting - I think our network is slow and hit post twice.

                      Implimented in a Calculaton field so it retrospectivley updates old records without my intervention.

                      The calculation field is in Invoices and is Text as are the corresponding fields in Photographers Details.

                      • 8. Re: Case Function
                        philmodjunk

                        The forum server is extremely slow and that's probably why we are seeing these duplicates.

                        So does this last post indicate that the calculation now works for you?

                        • 9. Re: Case Function
                          gavjuan

                          No, it still won't default if a target field is empty. As there are is only a small number of artists I could use the default data in the empty fields as a work around. Not the right solution but it would work. My knowledge of formulas is limited but generally I get there by trial and error - not this time.

                          • 10. Re: Case Function
                            Timothy Bentley

                            Maybe try

                            Case  (Currency = "$"; Photographers Details::Details Dollar ; Currency = "€"; Photographers Details::Details Euros ; Currency = "£"; Photographers Details::Details Sterling ; not IsEmpty ( Photographers initials ) ; Photographers Details::Details Sterling )

                            • 11. Re: Case Function
                              philmodjunk

                              Then one question is not yet answered though you tried to answer it:

                              What is the context of that calculation? invoices?

                              "Context" is not the name of the table in which it is defined. "Context" is the table occurrence name selected in the drop down at the top of the specify calculation dialog where you define this calculation. Is "invoices" selected in that drop down?

                              What value are you getting at the time this fails to work for you? Are you getting an empty field or ???

                              If you put an unsorted, unfiltered portal to photographers details, what do you see in the Details Sterling field of the very first portal row?

                              • 12. Re: Case Function
                                gavjuan

                                Sorry, my misunderstanding. You are correct - the context is 'Invoices' and I'm getting an empty field.

                                I would need to make a portal and set it up that way - perhaps that's the best method?

                                I've done that and I see the correct Sterling details. How should I proceed from here?

                                I currently have fields for the 3 most common currencies and anything else defaults to Sterling.

                                I hope I'm making it clear for you?

                                • 13. Re: Case Function
                                  gavjuan

                                  Not successful, still returning empty field instead of default result.

                                  • 14. Re: Case Function
                                    philmodjunk

                                    The portal was to be used as a test to see what data appears in the portal so as to identify why the calculation isn't working as expected. My best guess is that you have a problem with your data not the calculation. What I see in your last screen shot appears to be a layout based on photographer details. Be sure to put a portal to photographer details on the invoices layout.

                                    What do you see in the Details Sterling field of the first record in that portal?

                                    Do you see more than one record in that portal?

                                    PS. matching up these records by photographer details can also be a source of the trouble here as you could easily end up with two photographers with identical initials. It's better to match by an ID number auto-entered as a serial number as a way to be sure that you are linking to the correct record in Photographer Details.

                                    1 2 Previous Next