9 Replies Latest reply on Jul 28, 2014 10:04 AM by malibux14x

    Searching records and omitting invalids

    malibux14x

      Title

      Searching records and omitting invalids

      Post


           Hey there.

           i am trying to set up a scrip that checks once a day all the records where i need to pay commissions.

           idealy, id like to set it up to where when i enter the layout (invoices) it runs through script trigger to:

           -search all invoices for anything that was shipped 17 days ago

           -put an "x" in the "commissions paid" field

           -have a box pop up that says "Its been 2 weeks since we shipped these Media Cubes" or whatever

           -but ignore anything that already has an "x" in the "commissions paid" field from previous searches.

            

           Im attaching a screen shot of the script that i had thus far.

            

           I know the second "else if" is where the problem lies.  if it sees an "x" it just exits the search.  i tried putting "omit multiple records if..." but that still didnt work.

            

           any thoughts on how to get this?

           thanks!

      Screen_Shot_2014-07-21_at_11.44.04_AM.jpg

        • 1. Re: Searching records and omitting invalids
          SteveMartino

               There are many things wrong with this.   What you want it to do, and what you are trying to do don't resemble each other at all.

               After you enter find mode, use set field to set your search criteria.  You can select and set multiple fields for an ''and'' search..

               You dont need any exit script steps.  After an 'else if' block of script steps (indented) are finished, the script drops down to the 'end if' step..  But the whole script needs to be done. The  only script step i would keep is enter find mode.

               It seems to me you need to do a find first of orders that were shipped greater then 17 days ago, AND  isempty  (Invoices::commissions paid)..

               -but ignore anything that already has an "x" in the "commissions paid" field from previous searches.  
               This is the part that confuses me is, what is the criteria to change the  Invoices::commissions paid field?  It makes no sense to set the field to 'X', just because you looked at it.

                

          • 2. Re: Searching records and omitting invalids
            malibux14x

                 Hey Steve.  thanks for taking a look.

                 well first i want to put an "X" in that field so that it doesnt bring that filed back up when i search to see whom i need to pay commissions to.  if there is an X, that means i already paid it.

                 i tried taking your advice but am still not getting the results im looking for which leads me to think i am wrong.  ha

                 can you take a peak at this image to see if im on the right track  thanks

            • 3. Re: Searching records and omitting invalids
              philmodjunk

                   You may find this thread of scripted find examples useful: Scripted Find Examples

              • 4. Re: Searching records and omitting invalids
                malibux14x

                     Awesome!  thank you so much.  i knew it was something simple.  i didnt set the field as "=" which signifies that the field is empty.

                      

                     works perfectly now.

                      

                     thanks

                • 5. Re: Searching records and omitting invalids
                  malibux14x

                       Hey Phil,

                       so it looks like this isnt complete.

                       i set the two field in individual "set field" querys and it pull anything with the "commissions paid" field empty when i want it to find any record that is over 17 days old and that field is empty. 

                       it seems to be going back 17 days but then finds all the records from then until today and then puts an "x" in that field.

                       any thoughts to make this work and any thoughts to make this better?  Maybe im looking at it the wrong way.

                       thanks a lot.

                  • 6. Re: Searching records and omitting invalids
                    philmodjunk

                         Is the layout based on Invoices when this script is perfromed? If it is based on a different table, you can get unexpected results sometimes.

                    • 7. Re: Searching records and omitting invalids
                      malibux14x

                           No this layout is all based on invoices. 

                           i am thinking that my "set fields" are not correct to properly search for what i am looking for but i am not sure the appropriate fixes.

                           thoughts?

                      • 8. Re: Searching records and omitting invalids
                        philmodjunk

                             What I see looks like correct criteria for what you want to find though I'd use less complex date criteria than what you are using (and you may have good reason for the date range that you are using).

                             What I see should find all records where the date in Invoices::Date Shipped is in the date range from 100 days older than today through 17 days older than today. ("<" & Get ( CurrentDate ) - 17  is what I would use here, but maybe you have a lot of records more than 100 days old that you want to ignore here...) and that also has an empty commissions paid field.

                             I suggest either using the Script Debugger in FileMaker Advanced to step through the script one step at a time to watch what is happening or to put a pause/resume script step immediately after the first perform find step so that you can see what records were found by your script before anything else takes place.

                             Do make sure that Date Shipped is a field of type date (or a calculation field with a date result type specified) and not text.

                              

                        • 9. Re: Searching records and omitting invalids
                          malibux14x

                               Doh!  I knew it would be something simple!!!

                               i didnt have the "date shipped" field set to a date but rather text.  grrrr.

                               works perfectly now.

                               thanks!