1 2 Previous Next 21 Replies Latest reply on Jul 14, 2013 8:40 AM by philmodjunk

    Can you use $$variables in a calculation field for finding a count?

    CarrieB.

      Title

      Can you use $$variables in a calculation field for finding a count?

      Post

           I would like to have a calculated field for Get ( FoundCount ) that omits records from the count that do not match a $$variable. Is this possible? I think I've read that you are not suppose to use $variables in calculations?

           Any thoughts are appreciated!!

        • 1. Re: Can you use $$variables in a calculation field for finding a count?
          JimMac

               you can use variables in calculations.   Each variable type has it's own restrictions.

               Jim...

          • 2. Re: Can you use $$variables in a calculation field for finding a count?
            philmodjunk

                 There are many ways to selectively count records in FileMaker, but such a calculation is not one of them. Please describe what you want in more detail so that we can suggest an alternative.

            • 3. Re: Can you use $$variables in a calculation field for finding a count?
              CarrieB.

                   The user is taken to a layout in Find Mode where they can enter a couple of search criteria. Then they enter a date range in a Custom Dialog box, which is turned into the $$variable, so it can be run across 3 date fields. When they get the search result, I would like to see a found count for 2 of the date fields, but only if the date falls within the $$variable they just entered into the Custom Dialog box.

                   Hopefully this makes sense. Please let me know. Thanks!

              • 4. Re: Can you use $$variables in a calculation field for finding a count?
                CarrieB.

                     Let me expand a bit more on the process. In my script, Perform Find happens first (using the criteria entered on the layout in Find mode), then the Found Result is constrained based on the date range $$variable. Don't know if this matters or not, but thought I would throw it out there. Thanks!

                • 5. Re: Can you use $$variables in a calculation field for finding a count?
                  philmodjunk

                       Don't see why you need to constrain the found set, the date range can be made part of the original find, but the resulting found set is the same either way.

                       Since you want three counts based on three different date fields, you'll need 6 more fields: 3 caclualtion fields that compute a value of 1 if the date falls in the specified range and 3 summary fields that each compute a total of one of your three new calculation fields. Only the summary fields need be visible on your layout.

                       Each of the three calculation fields can be set up like this, only the name of the date field changes:

                       YourDateField > GetAsDate ( LeftWords ( $$Variable ; 1 ) ) AND YourDateFIeld < GetAsDate ( RightWords ( $$Variable ; 1 ) )

                       Make this an unstored calculation by clicking storage options and selecting that check box in the dialog that pops up. Select Number as the result type.

                  • 6. Re: Can you use $$variables in a calculation field for finding a count?
                    CarrieB.

                         The reason I am constraining the Find is that it only constrains the found set if the user entered dates in the Custom Dialog box. Otherwise, it just runs the Find. I'm probably not doing it right. I really have no idea what I'm doing here. :)

                         I only want found results for two of the fields, so I made them. One of them works great. The other does not. I'm not sure why. I made them both the same. However, there are times where the second field will not have a date. Does this affect the result?

                         Also, what if $$Variable is empty. Then the Found Count won't calculate. Is there a way to make it count all of the records if $$Variable is blank?

                         Thanks again! Sorry I don't really know what I am doing here. :)

                    • 7. Re: Can you use $$variables in a calculation field for finding a count?
                      CarrieB.

                           Looking at it some more, here is what I need:

                           If $$Variable is not empty, I want it to calculate all the records that fall in the date range.

                           If $$Varible is empty, I want it to count all records that have a date in the field, but skip the ones that don't.

                      • 8. Re: Can you use $$variables in a calculation field for finding a count?
                        CarrieB.

                             I think I got it. But, still happy to hear your thoughts. I'm sure there is a better way to do it. Thanks!!

                        • 9. Re: Can you use $$variables in a calculation field for finding a count?
                          philmodjunk

                               But HOW did you get it? Since I dont' know exactly how you implemented the suggestions I've made, I don't have any thoughts on this to share.

                          • 10. Re: Can you use $$variables in a calculation field for finding a count?
                            CarrieB.

                                 Sorry about that. I'm a bit scatter-brained this morning. Trying to do too many things at one time. I used the following calculation for Date Field 1:

                                 If ( IsEmpty ( gStartDate ) ; Count ( CovenantSent ) ; CovenantSent ≥ GetAsDate ( LeftWords ( $$date_search ; 1 ) ) and CovenantSent ≤ GetAsDate ( RightWords ( $$date_search ; 1 ) ) )

                                 It works for the first date field, but I still can't get the second date field to calculate. Could it be because sometimes it is empty? I need to do a bit more research, but I've run out of time at the moment.

                                 Thanks!

                            • 11. Re: Can you use $$variables in a calculation field for finding a count?
                              CarrieB.

                                   gStartDate is part of what creates the $$Variable. If the user doesn't enter any dates, it will be empty.

                              • 12. Re: Can you use $$variables in a calculation field for finding a count?
                                philmodjunk

                                     You have not implemented the calculations that I suggested correctly. The count function does not work like you are expecting it to and that's why I didn't refer to that function in my suggested solution. You need to implement them exactly as I described. There are no if functions used, only boolean expressions that return 1 for true or 0 for false. The summary fields then total up those fields to produce a count of the records where the specified date field falls in the specified date range.

                                     Also, if the user is specifying dates in global fields, you really don't even need the variable, you can keep the data in two global date fields and your find script can use them directly. This also makes possible simpler calculation fields for selectively counting your records.

                                     Here's a thread of sample scripts that perform finds that refer directly to the values in global fields. There's an example there that finds on a date range with two dates entered into two global fields: Scripted Find Examples

                                • 13. Re: Can you use $$variables in a calculation field for finding a count?
                                  CarrieB.

                                       I originally entered your calculation exactly as you had it and it still doesn't work in the 2nd date field. Do the empty fields affect that calculation?

                                       The "If" part of the calculation works for me in the first date field. If that is not the correct way to do it, how do I get it to calculate if the $$variable is empty? The user has the ability to search by other criteria without including date ranges.

                                       I used the variable based on someone's advice in a previous post I did regarding searching across the 3 date fields. Because the user enters a start date and end date, which creates the variable of "StartDate...EndDate". I will check out your link and see if I can make this better.

                                  • 14. Re: Can you use $$variables in a calculation field for finding a count?
                                    philmodjunk

                                         I don't think your first calculation CAN work correctly. It may create that appearance with your initial tests, but Count ( CovenantSent ) is only referencing data in a single record, not a count based on all records in your found set.

                                         If the variable is empty, the result of the original calculations will be 0 and you will get a summary result of 0 records. If you want the field to be empty, then use an if function, but one that is much simpler: If ( Not IsEmpty ( $$variable ) ; //My original calc goes here ). And as I stated previously, if you have a user entering these dates into global date fields, I wouldn't use the variable at all, I'd just refer to the data in the global fields. Then I can replace GetAsDate ( LeftWords (.... with just a reference to a global date field.

                                         If the calculation does not work for your second date field, make sure that it is a field of type date.

                                    1 2 Previous Next