7 Replies Latest reply on Jul 9, 2013 11:02 AM by philmodjunk

    script with variable field entry



      script with variable field entry




           I am trying to write a script that will first find a particular field and then perform a calculation based on the information there.  The steps are basically:

           Select last occupied field (X)

           If (X) meets ABC criteria, perform this function

           However, I can't figure out how to write the script so that it will enter the X field into the If-then statement.

           Also the criteria is "Is the date in field X more than 1 year prior to todays date?"  I can't figure out how to get the program to check that either.  Any help would be greatly appreciated.  Thank you in advance.



        • 1. Re: script with variable field entry

               Please describe what you are trying to do in more detail and give an example if possible.

               What do you mean by "last occupied field"? If this is the field that is currently "active"--the cursor is still in the field, There are get functions that will return the table (occurrence) and field name of the currently active field. And the function GetField can be used with that data to access the contents of the field.

               But maybe you mean "current Record" instead of "last occupied field" and then there are other methods that can be used.

               The expression:

               Let ( T = Get ( CurrentDate ) ; Date ( month ( T ) ; Day ( T ) ; Year ( T ) - 1 ) )

               will compute the date 1 year earlier than today.

          • 2. Re: script with variable field entry

                 Thank you Phil.

                 What I meant was this: I have 40 fields ("Steps") that get filled in, in order, on the same day every year.  I'm trying to automate the process.  So for example:


                 Step 1: 9/1/2010

                 Step 2: 9/1/2011

                 Step 3: 9/1/2012

                 Step 4: (empty)

                 ...so on to Step 40


                 So "Step 3"  is the last occupied field, the last field with information.  I would like a script that, on 9/1/2013, will automatically fill in Step 4. So the script has to find the last field with information, check if that date is more than 1 year prior to today (thank you for that expression) and then add one year and fill in Step 4.  

                 What I can't figure out is how to write the If-Then statement without specifying a field by name.  Any ideas?

                 Thanks again for responding!


            • 3. Re: script with variable field entry

                   While it's possible to compute a fields's name and use the GetField function to read the data in it as well as using Set Field By Name to modify the data in such a field, I'd set up a table where all these "steps" make up individual records in the table. This replaces "40 fields" with just one field, but up to 40 records.

                   That approach would eliminate the whole issue of trying figure out which field to access, you'd just perform a find or use a relationship to access the record with the most recent date. Where you have empty fields, the approach I am recommending simply wouldn't have any records at all, you can create the next record only when needed.

              • 4. Re: script with variable field entry


                     This is an interesting idea, but I don't think it would work for my situation.  I have over 600 records, each with unique "Step" information.  I want each one to update independently.  Unless there is an easy way to transfer all of that informaiton into a table, I think it would be a huge waste of man-hours to re-enter all that data.  I realize it would have been more elgant if I'd created a table from the beginning, but seeing as the damage is done, I'd prefer to find a way to work with what I have.

                     Can you explain a bit more about using the GetField function?  I appreciate your help

                • 5. Re: script with variable field entry

                       GetField is fully documented in FIleMaker help.

                       There is, in fact fairly simple ways to move your data from your current fields into individual related records. It will take some time to set up, but the resulting improvement in your design is unlikely to be a "huge waste of man hours" as it may resolve a number of other potential complications in working with your database.

                       But it isn't a step to take without a careful overview of your entire database design and intended function as it is a radical modification of the current design that could affect how many different areas of your solution. That's not something I can do from here so you'll have to decide for yoursefl.

                  • 6. Re: script with variable field entry

                         I had meant that manually re-entering the data would be a waste.  

                         I appreciate all your help with this.  Thankn you!

                    • 7. Re: script with variable field entry

                           A script or repeated use of Import Records can move the data from individual fields into records of a related table.