1 2 Previous Next 23 Replies Latest reply on Oct 12, 2011 11:47 AM by dg3321

    If statement question

    dg3321

      Title

      If statement question

      Post

       How do you write an if statement if there are two factors that need to be true in order for the desired result to happen? I have a basic expenses/credits ledger going whereby I have a field for each expense category separate from my expense/credit portal.  In one category field, I want it to total the amount expensed towards this category in the month of January. The way I thought this should go is as follows: Category A amount field:  If (Category = A and Date = 1/*/*, expenses, "") Category A summary field: = Total (Category A amount field) The error I'm getting is at the end of the 1st if statement.  How should this read? Thank you!

        • 1. Re: If statement question
          revmk

          Hi dg3321,

          Can you post a little more information about the setup.

          From what you are saying and is the correct orperator, but are you returning the text "expenses" or a field called expenses?

           

          Rev

          • 2. Re: If statement question
            philmodjunk

            Wild cards don't work in an if statement so you'll need to change this part: Date = 1/*/*

            Depending on your system settings that 1  could represent the month of january or the first day of the month. Assuming that it is the day, you can use: Day ( Date ) = 1 instead.

            • 3. Re: If statement question
              dg3321
               Rev MK:  that is a field called expenses
              • 4. Re: If statement question
                dg3321
                 PhilModJunk:  How do I setup my settings so that 1= January, 2=feb, etc.
                • 5. Re: If statement question
                  philmodjunk

                  Month ( Date ) will return the number of the month. Just as Day ( Date ) returns the day number of the date.

                  • 6. Re: If statement question
                    dg3321

                    That worked perfectly, thanks!  I only have one more hurdle to clear and then i'm all set.

                    I currently have:

                    Category A_January Amount: If (Category = A and Month (Date) = 1; Expenses ; "")

                    Category A_January Total: = Total of Category A_January Amount

                    The Category A_January Total field is correctly only adding the expenses from the month of January, however it is not updating automatically.  I've noticed that it only updates the total when i go to the next portal row and insert my date field and then tab to the expense amount field, its THEN, that the total field is updated with the figure from the prior portal row.

                    What settings do i need to change in order for this to update automatically as the number is entered into the expense amount field in the portal?  The 'amount' field above is currently a calculation field.  Should i change it to a number field and enter in the calculation that way?

                     

                    Thanks!

                    • 7. Re: If statement question
                      philmodjunk

                      This is a known refresh issue with summary fields from a related table. You can set up script triggers that refresh the window when you exit a field or the portal row.

                      You may also want to greatly simplify this approach if you are using Filemaker 11, a single summary field can produce monthly totals for each month if you set it up in 12 one row filtered portals instead of defining 12 calculation fields and 12 matching summary fields...

                      Another option is to define calculation fields in the parent record that use Sum in place of the summary fields defined in the portal's table. This avoids the need for a refresh script, but cannot be set up with portal filtering as it will evaluate at the data level rather than the presentation level.

                      • 8. Re: If statement question
                        dg3321

                        Ok, thanks.

                        How do i set up the script trigger?  I found the 'refresh window' option for the script but i'm not sure how to set it up from there.

                        • 9. Re: If statement question
                          philmodjunk

                          On any field that you can edit on your layout and which then might alter the total computed by the summary field, use the OnObjectSave trigger to perform one these two scripts:

                          Commit Record
                          Refresh Window

                          Commit Record
                          Refresh WIndow [Flush cached join results]

                          I think the first two steps are sufficient, but if not, try the 'flush' option and see if works where the other doesn't.

                          • 10. Re: If statement question
                            dg3321

                            Which field should i be putting the script trigger on?  The summary field or one of the fields in my portal?

                            • 11. Re: If statement question
                              philmodjunk

                              Not the summary field. any field you have in the portal where changing the value in that field might change the value in the summary field.

                              • 12. Re: If statement question
                                dg3321
                                 Ok, I tried that (both scripts) and it didn't work.  Any other ideas?  Could you further explain how to set up the portal summary you mentioned earlier?  Thank you so much.
                                • 13. Re: If statement question
                                  philmodjunk

                                  Not really and they've worked for me in the past. Some detail is missing here. You might try enabling the script debugger if you have advanced or adding a show custom dialog step to the beggining of the script and then working with this layout to confirm that the script is actually be triggered at the times it needs to be triggered.

                                  If we can't figure out the refresh issue with one instance of the summary field, the filtered portals with summary field method will have the same issue 12 times over.

                                  How it works for January and then extending this to 11 more portals just requires a slightly different portal filter expression for each:

                                  1) Define a summary field that computes the total of Expenses instead of the calculation field with the If function.

                                  2) Set up the portal filter expression to apply the same logic as the original If function:

                                  Portaltable::Category = A and Month (PortalTable::Date) = 1

                                  3) Make this a one row portal and make the summary field the only field in the portal row.

                                  4) Once this works for January, make copies of this portal and update the Portal filter expression to specify different months and/or different categories.

                                  • 14. Re: If statement question
                                    dg3321

                                    So i'm not really sure why this didn't work the first time, but i saved the file and returned to it this evening and i assigned the refresh script to my 'expenses' field in the portal (with the refresh script using commit records then refresh window w/ flush cached join results) and set it to trigger upon object exit and it worked!  The summary field updated immediately.  Also, i figured out that if i assign the refresh script to the date field, category field, expenses field and credit field, the running balance does not disappear and it updates immediately...both problems solved w/ one script!

                                    Thank you as always for your time and patience.

                                    Best,

                                    David

                                    1 2 Previous Next