8 Replies Latest reply on May 15, 2015 8:16 AM by mrb13676

    Novice trying to find records from current month.

    BrianClemens

      Title

      Novice trying to find records from current month.

      Post

      I have a field called "Shipped" that holds the date that a vehicle ships.

       

      I would like to have a find that will show only the units that shipped in the current month.

      Ultimately I would like to click a button that prompts me for the month & year and then it returns the records accordingly.  That way I can look at how many units shipped per month, by month.

      Can I get all of that off of the "Shipped" date field or do I need to pull out the month & year in calculated fields and find off of those?

        • 1. Re: Novice trying to find records from current month.
          philmodjunk

          Define two global fields for month and year for holding the user's selection, then use a script like this:

          Show Custom Dialog ["Enter Month Number and Year" ] //specify Yourtable::gMonth and YourTable::gYear as input fields
          If [Get (LastMessageChoice ) = 1 //OK was clicked]
          Enter Find Mode[]//clear the pause check box
            Set Field [YourTable::ShippingDate ; "*/" & Yourtable::gMonth & "/"& Yourtable::gYear]
            Set Error capture [on] //keeps FileMaker dialog from interrupting script if no records are found
            Perform Find[]
          End If

          * is a wild card so that you do not have to specify the day. This only works if gMonth and gYear have global storage selected in Field Options.

          You can also set up gMonth and gYear as fields with value lists on a layout so that the user selects the values, then clicks a button. Your script would then not use the Show Custom Dialog. (This method makes for easier error handling as you can't check for errors such as a month number of 13 until the user clicks OK to close the custom dialog.)

          • 2. Re: Novice trying to find records from current month.
            BrianClemens

            I set gmonth & gYear as drop down lists.

            I'm not quite sure how to enter the  Set Field section.  I am in the script, I'm not sure what to do at the semi-colon after shipping date. 

            • 3. Re: Novice trying to find records from current month.
              philmodjunk

              This is a common newbie question.

              When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

              • 4. Re: Novice trying to find records from current month.
                BrianClemens

                Thanks for that, I am past the semi-colon part.  I'm a little confused on the [Get (LastMessageChoice) = 1 //OK was clicked]

                I've got the IF function and Get, but is the lastmessagechoice referencing something else or an actual command?

                 

                • 5. Re: Novice trying to find records from current month.
                  philmodjunk

                  Get ( LastMessageChoice ) returns the number of the button clicked to close the custom dialog. The //OK was clicked is a comment you can add inside this calculation to make your script easier to understand. In calculations, anything to the right of // will be ignored when the calculation evaluates.

                  Get is a very, very useful function with many different results possible depending on what you put inside the parenthesis. It's a good one to look up in FileMaker help to see all the possible choices. When you first look at the list of functions in the Specify Calculation dialog, you'll find just one entry: Get ( Flag ). "Flag" is a placeholder for this very large list of possible parameters you can specify for this funciton. If you select "Get Functions" in the "View" drop down just above this list of functions, you'll get a list of all the possible ways a get function can be used in a calculation.

                  • 6. Re: Novice trying to find records from current month.
                    BrianClemens

                    Everything seems to be working, but no records are shoing up.  I have a few records shipped this month but it is not pulling anything up.

                    is the gMonth dYear pulling into out of the ShippedDate or do i need to pull the month & year out of the shipped date

                     

                    • 7. Re: Novice trying to find records from current month.
                      BrianClemens

                      OK, got it to work.  I think it was the order of the SetField.  My dates are set as Month-Day-Year.  Once I switched that, it seemed to work so far.

                       

                      Thanks for all the help.  My last days of database working were Approach.  long time ago.

                      • 8. Re: Novice trying to find records from current month.
                        mrb13676

                        Ok, resurrecting this ancient thread. I've had a look at this and tried to get it to work, but no joy. 

                        Using FM14.

                        Have corrected the SetField calculation to reflect our date format YYY/MM/DD

                        Running the script does nothing. 

                        COuld this be due to the fact that my date in the records is a date field (and thus stored as an integer)?

                        Mike