Jump to content

Find by another field containing year

Recommended Posts

Hi FM people!

I have one problem which is probably simple to solve but so far solution eluded me.

I have a dropdown text field which offers a list of years (Table::Years). This is global field.

I have very simple find script:

Enter Find Mode [Pause: Off]
Set Field [Table::Order Date; Table::Years]
Constrain Found Set [ ]

Table::Order Date is a date field.

Idea is to perform find according to the value chosen from a dropdown list. However, this does not work. I'm getting "provided find criteria not valid".

But, if I enter manually one of the years from the list, It works. Same is valid if I write down a year in the script e.g. Set Field [Table::Order Date; 1980].

Set Field [Table::Order Date; Table::Years] itself works when in browse mode, but in find mode, nope

This is I guess a format issue, so I tried several combinations changing fields to text or date but without success.

Any help is appreciated

Edited by Sinky
Link to post
Share on other sites

Is Table::Years a global field? If not, then it has no value once you have entered Find mode and your Set Field step does nothing. 

I am also puzzled why it is named Years. Can it contain more than one year? If so, you cannot use it for find just as it is. 


Edited by comment
Link to post
Share on other sites
Posted (edited)

Yes...it's global field. It's called "years" because it's a dropdown list that contains a list of years.


Edited by Sinky
Link to post
Share on other sites
16 minutes ago, Sinky said:

Yes...it's global field

Then I don't know how to reproduce your problem.


16 minutes ago, Sinky said:

It's called years because it's a dropdown list that contains a list of years.

A field does not contain a dropdown list. It contains the value (or the values) you have selected from the drop-down list attached to the field.


Link to post
Share on other sites

You're right....so, the field contains one value which is chosen via drop-down list attached to the list

Tnx for trying!

Link to post
Share on other sites

For what is worth, I think I found the source of the problem. Table::Years field was not included in the Table view layout. 

Link to post
Share on other sites
1 minute ago, Sinky said:

I think I found the source of the problem. Table::Years field was not included in the Table view layout. 

If it was not on the layout, then how did you make the selection?

In any case, that cannot be the cause of the problem you described. The Set Field [] script step works entirely at the data level and does not depend on the presence of any field on any layout.  


Link to post
Share on other sites

The filed was on the Form layout.

Well, you're right.... I can't recreate the problem but I don't understand how I managed to solve it.


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By wfcperrine12
      I have searched the web for a Custom Function that solves this to no avail…  There are many CF’s that “almost” do what I need but nothing as explained below.
      Calculate a DATE x days AFTER a given date skipping weekends AND bank holidays.
      Result Desired = Date that skips weekends and bank holidays.
      Any year’s HOLIDAY dates can be gleaned from pre-existing Custom Function “HolidayListByYear (yearNumber)” written by Jonathan Mickelson at www.briandunning.com (also uses another Custom Function he wrote called “DateByDayOccur”).
      Banking holidays should always include:
      * When a holiday falls on a Saturday, it is usually observed on the preceding Friday. When the holiday falls on a Sunday, it is usually observed on the following Monday.
      New Years Day
      Jan 1
      Martin Luther King Day
      Jan 20
      President’s Day
      Feb 17
      Memorial Day
      May 25
      Independence Day
      July 4
      Independence Day (observed)
      Labor Day
      First Monday in September
      Columbus Day
      Second Monday in October
      Veterans Day
      Nov 11
      Thanksgiving Day
      4th Thursday in November
      Christmas Eve
      December 24
      Christmas Day
      December 25
      New Year’s Eve
      December 31
      StartDate = 12/30/2020
      NumbOfDays = 2
      Result would be= 1/3/2021
      NOTE: Must skip Dec 31 (New Years Eve/weekend day), Jan 1 (New Years Day/weekend day)
      StartDate = 1/15/2021
      NumbOfDays = 2
      Result would be= 1/20/2021
      NOTE: Must skip weekend AND Jan 18, MLK Day
      StartDate = 7/2/2021
      NumbOfDays = 2
      Result would be= 7/7/2021
      NOTE: Must skip weekend and July 5 (July 5th is the observed day for Sunday, July 4, 2021)
      SPECIAL NOTE:  The custom function must be able to handle year crossovers such as EXAMPLE 1 above (going from 2020 into 2021.)
    • By emncwundy
      I have two iPads connected to FM server. For some reason one ipad (5th Gen running 13.5) will not complete the navigation scripts and shows error messages (no records match this find criteria), however the 7th Gen running on 13.1.1 works fine..... Issue has only begun after purchasing the second, newer iPad. I cannot for the life of me work out what is wrong, or how to troubleshoot and would love some guidance please!! Here is a screengrab of one of the scripts and related layout:
      fm go script issue.tiff Orders iPad layout.tiff
    • By gczychi
      I have a Number field «Kontakttyp_enum». It contains multiple (!) number values, depending on what the user has deemed to select (see picture). It works great — numbers appearing and disappearing in this field, depending on which checkboxes are clicked upon.
      Now, I’d like to set this field with the «Set Field» script step — but I can’t figure out how. I can read specific values (for example with the function «Position ( Kontakttyp_enum ; 6 ; 1 ; 1 )», but I don’t know how to add or remove a specific number.
      Has anybody any insight on this?
      It’s quite interesting. Even though it is a number field, the numbers seem to be delimited by Return characters (¶) — at least this is the way the field content is displayed on the layout. But when I insert a return-delimited list, the checkboxes break. For example, when I insert «1¶2¶3», no checkbox is crossed. And when I then click a checkbox, the same number is inserted again.
      Thanks a lot for any hints!

    • By ken_s2007
      I arrived at a solution that is working now.
      The problem I was trying to solve:
      I have a field containing a bar code number, and want to have a find executed against the data, but if the first two characters of the barcode were "AB", to strip those off before executing the find.
      The solution:
      Use a custom dialog box with user input to scan the barcode; store the results in a global field; run an If statement, and if the first two characters are "AB", set a variable to the value minus the left two characters; perform a find using that variable.
      Following is what I tried before the working version. I'm leaving it here mainly because I'm still trying to understand more (in my general FileMaker knowledge) about PROBLEMs 1 & 2 (#3 has been solved by using the custom dialog with user input, etc.). But since I have a working solution, only have a look if interested. thanks
      I have a field containing a bar code number, and a button that runs a script to perform a find on that field, where the user scans the bar code, and the scanner returns the number plus a carriage return (I have the scanner programmed that way) to  execute the find. When there is a record with a bar code label whose number below the bar code itself matches (e.g., "20154673"), the following script works just fine.
      Enter Find Mode [ Pause: Off ] Go to Field [ Select/Perform ; hardware::id_num_org ] # At this point, the bar code is scanned, and the CR # triggers the find. However there are some barcodes begin with "AB" which are not to be used in the records (business rules). When using "Enter Find Mode [Pause: Off ], "Go to Field [ Select/Perform ... " is then executed before the barcode is entered; if pause is On, the user has to manually select the field to enter in (unless there was an active record, and the target field was active) -PROBLEM 1.
      If a record is not found due to the leading "AB", the "No records match this criteria" dialog is suppressed (as I want with error capture on), but the error code returns "0", not "401"—UNLESS I use "Perform Find [ Restore ]" with the criteria being data beginning with "AB", but of course the data being searched on is derived from the barcode scan. -PROBLEM 2.
      (Note: I simplified debugging of the error code by temporarily commenting out the If structure, and placing a custom dialog at the end of the script, using "Get Get ( LastError )" ) .
      Once I can (hopefully) get into the If structure OK, how can I modify the data that was input during the search? I have the code for "Set Field" (or similar) worked out, but don't know how to capture the input data into a variable, or what have you. -PROBLEM 3.
      Following are the basics of the script so far:
      Set Error Capture [ On ] Enter Find Mode [ Pause: Off ] Go to Field [ Select/Perform ; hardware::id_num_org ] If [ Get ( LastError ) = 401 ]   # I only want to run the steps inside the following # If statement if the scan begins with "AB";   # not if there simply is no record for the barcode. If [ Left ( hardware::id_num_org ; 2 ) = "AB"      Modify Last Find      # I think in order to not require the user to # rescan if we get to this If statement, that I may # need to capture the scanned input into a variable. # That is yet another thing to work out.      Set Field [ hardware::id_num_org ; Right ( hardware::id_num_org ; Length ( hardware::id_num_org ) - 2 ) ]   End If End If Set Error Capture [ Off ] I tried using Perform Find [ ] , but didn't see a way to pass the scanned barcode using criteria for "Specify Find Requests".
    • By Richard Carlton
      Summer is over and our weekly FileMaker webinars are back! 

      Our first one is this Thursday at 11am PDT, on “Relationships for Beginners”. 

      Register Here: 

      We will spend 30 minutes covering a specific topic. Then the last 30 minutes will be an open Q&A on any topic.

      Topic List:
      Sept 13th: Relationships for Beginners
      Sept 20th: Find Records, Date Ranges, and Special Operators
      Sept 27th: Conditional Formatting (Can I making something Red?)
      Oct 4th: Automation (Repetitive Tasks can become Scripts)
      Oct 11th: Reporting & SubSummaries
      Oct 18th: Merge Fields and Hiding Objects (Harry Potter Invisibility Cloak)
      Oct 25th: Sharing your FileMaker App with Co-Workers
      Nov 1st: Basic Concepts for Building Mobile Apps

      All the best, 
      Richard Carlton 
      CEO & Video Trainer
  • Who Viewed the Topic

    5 members have viewed this topic:
    camacorp  vinnygi  pjreagan  Andreas T.  Chris Gilbert 
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.