13 Replies Latest reply on Oct 26, 2014 8:14 PM by philmodjunk

    Omitting records containing no date

    WillEvans

      Title

      Omitting records containing no date

      Post

      I'm trying to constrain a found set such that the records that don't contain a date in the date field aren't shown.

      I've had a go using "=" but I'm told by FM that I need to write my criteria in date format given that it's a date field. 

      What criteria can I use for this instead? 

        • 1. Re: Omitting records containing no date
          raybaudi

          Hi

          have another go using = ( without quotes )

          • 2. Re: Omitting records containing no date
            WillEvans

            Thanks for your help raybaudi. That seems to work for one of the date fields I have but not for the other, weird. 

            I'm also trying to omit records that have a date that is before a certain date. I have tried saying '=<01/01/2014' and just '<01/01/2014' but neither seem to work. Any ideas? 

            Ideally, I would like just to actually say only show records with a date that is within the last 3 months of today's date. Is that possible? 

            • 3. Re: Omitting records containing no date
              philmodjunk

              Make sure that your fields are really date fields and not text fields.

              • 4. Re: Omitting records containing no date
                WillEvans

                Thanks PMJ, I realised my error was putting the date criteria together with an AND function for another omit record criteria. I thought that by doing so both omit record criteria would be performed independently, but that's not the case. 

                Is there any way of telling FM to only show dates that within the last 3, say, months? So it's constantly adjusting based on today's date? 

                • 5. Re: Omitting records containing no date
                  philmodjunk

                  There are a number of ways that you can use Set Field with a calculation that specifies a range of dates and there is more than one way to define a "month" as it can be a 30 day interval or a calendar month. Here's one example:

                  Enter Find Mode []
                  Set Field [YourTable::YourDateField ; //see calc shown below ]
                  Set Error Capture [on]
                  Perform Find []

                  The calculation:

                  Let ( T = Get ( CurrentDate ) ;
                         Date ( Month ( T ) - 3 ; 1 ; Year ( T ) ) & "..." & Date ( Month ( T ) + 1 ; 0 ; Year ( T ) )
                        ) // Let

                  This scripted find is patterned after the other examples found here: Scripted Find Examples

                  Caulkins Consulting, Home of Adventures In FileMaking

                  • 6. Re: Omitting records containing no date
                    WillEvans

                    Awesome, thanks PMJ it worked a treat. 

                    One day I plan to truly understand how a let function works! 

                    • 7. Re: Omitting records containing no date
                      WillEvans

                      Hi Guys,

                      Just had a quick question regarding the date Let function above, which I would like to make a slight adjustment to. 

                      Now I would like to perform the find inside a field where the date format is simply YYYY.MM as opposed to the MM.DD.YYYY above. 

                      How can I adjust the code so it searches for the current month only in the field with that date format? 

                      • 8. Re: Omitting records containing no date
                        philmodjunk

                        If the field is of type date and what you describe is simply the data format specified for the field, the above function still works as designed as it works with the value of the field and not how it is formatted on a particular layout.

                        • 9. Re: Omitting records containing no date
                          WillEvans

                          Right, thanks PMJ, it turns out the field is in fact a simple text field and not a formatted date field. This surprises me as you can still do searches like... 2014.03...2014.08 and it will return records for the months 2014.03 through 2014.08. 

                          I have tried just changing the field to a date field, but as I expected, that produced a host of problems. 

                          Is there a way of getting the Let function to produce a result in a text field that finds records in the current month only? So, for example, this month I would just want it to create text to find for 2014.10 records and next month to automatically change to 2014.11 

                          As ever, thanks for your help PMJ. 

                          • 10. Re: Omitting records containing no date
                            philmodjunk

                            2014.03...2014.08

                            Fit's the correct format for data range searching as  the ellipsis operator, ... , works on other data types than just dates.

                            Year ( Get ( CurrentDate ) & "." & Right ( "0" & Month ( Get ( CurrentDate ) ) ; 2 )

                            would seem to produce the "this month, this year" value that you need.

                            You could also use replace field contents to convert your data from text to date if you did it in a few steps.

                            • 11. Re: Omitting records containing no date
                              WillEvans

                              PMJ,

                              The code works! Many thanks. Couple of things:

                              - It says there is a parenthesis missing, I put it after the first (Current Date), I think that's right but let me know if not.

                              - Also, please could you explain to me the significance of the '2' at the end of the code? I would love to understand better what's going on.

                              Also, why did you need to use a Let function in the previous code and not in this one? Is there a place I can learn about stuff like this? 

                              • 12. Re: Omitting records containing no date
                                WillEvans

                                What do you know, I need your help again :/

                                To give you some context, I'm creating a report/layout for current month performance. Your code has allowed me to only display records for the current month which is great. However, within those records I would like to sum the total of all the records which have a negative value within a particular field. Some of the current month records have a positive value, some have a negative value. I just want to have a field that gives a total of all the negative values for that particular month.

                                I've tried using a summary field, but it seems that would just aggregate all found fields, and I can't constrain the fields it aggregates without actually constraining the records out of the report entirely (using a script or something). Right? So presumably I need to use a calculation? If so, do you have any advice on how I could formulate such a calculation? 

                                • 13. Re: Omitting records containing no date
                                  philmodjunk

                                  You don't have to use a Let function in either case. Let, as used here, is a way to simplify the calculation by computing a value once and then using it in several different parts of the complete calculation.

                                  You are correct in where the missing parenthesis is placed.

                                  The 2 is so the right function returns the rightmost 2 characters. The expression inside the expression will be either 3 or 2 characters long: 020 vs 01. This is a commonly used method to insert leading zeroes where needed.

                                  You are partly correct on the summary fields. You can also sort to group your records and by using a sub summary layout part to display the summary field, the summary field can return a subtotal based on just that group of records.

                                  One way to separate negative and positive values (without resorting to a sub summary layout part), is to add a calculation field that only returns the negative values an returns null when the value is positive. A summary field can then sum this positive value:

                                  If ( Field < 0 ; Field )

                                  ExecuteSQL can also be used to compute such a value.