10 Replies Latest reply on Jul 17, 2014 12:42 PM by oman

    Auto Generate Records Based on Field Calculation



      Auto Generate Records Based on Field Calculation



           I have three tables: Objects, Maintenance Issues, Recurring Events.

           I'd like to auto-generate new Maintenance Issue records based on an "Overdue" field within the Recurring Events table. The overdue field is a number calculated from its duration length and last date completed.

           Is there a way for the database to automatically go through all Objects that have overdue Recurring Events and automatically generate new Maintenance Issue records for each overdue event?


           Thanks for all your help!

        • 1. Re: Auto Generate Records Based on Field Calculation

               You can set up a script that finds all records matching this criteria. After finding the records and generating the new records you need, you'll want to "mark" the records in some way so that your script doesn't find them again. (The value in the mark field would be one of your find criteria.)

               Here are some examples of scripted finds you might find useful: Scripted Find Examples

          • 2. Re: Auto Generate Records Based on Field Calculation

                 I cannot get the find to work with any of the linked script examples.

                 I have a field called Last Date. This is a manually entered date field.

                 Then there is text field called Rate. Also manually entered.

                 Third field is called Next Date. This is a date field that is computed by adding the Rate to the Last Date.

                 Fourth field is calculation field called Overdue. This is computed by subtracting CurrentDate (function) from the Next Date field.


                 I want to do a scripted find that returns everything with a overdue number greater than -10 (i.e. anything that is overdue, plus things that are 10 days from being officially overdue).

                 So far I have:

                 Enter Find Mode

                 Set Field [Table::Overdue; Table::Overdue >-10

                 Perform Find

                 I must be doing something wrong. A lot of the examples suggest a global field, but I don't think that works for me as I need different values for each record based on the last date and next schedule date. Any help would be appreciated. If I do a manual find in the overdue field everything works perfectly.

            • 3. Re: Auto Generate Records Based on Field Calculation

                   I think I figured out one method at least from another example.

                   Set Field [Table::Next Date; "<" & Get (CurrentDate)-(-10)]

                   Seems to be working and returning everything with an Overdue number of -9 or higher.

              • 4. Re: Auto Generate Records Based on Field Calculation

                     Make sure that the Result type specified for overdue is number, not text.

                     The set field step should the be:

                     Set Field [Table::Overdue; ">10"]

                     This assumes that you want to find all records where the value in Overdue is 10 or larger.

                     And the actual script should include at least one more criterion set to a different field to avoid finding the same record twice:

                     Enter Find Mode []
                     Set Field [Table::Overdue; ">10"]
                     Set Field [Table::Processed ; "=" ]
                     Set Error Capture [on]
                     Perform Find []

                     This assumes that the field I named "Processed" is empty if this record has not already been found by this script and used to generate a new record.

                     Note: once you have a found set of records that you need, your script can loop through the found records and use the data in each record to create the new records that you want to create from each.

                • 5. Re: Auto Generate Records Based on Field Calculation

                       Great that worked for the find. I'm a little lost on the loop through records and record creation. The records need to be generated in a different table, but using data from a field in the original table.

                       So Table 1 is where the find takes place.
                       For every record that is found, a new record should be created in Table 2.

                       The new record in table 2 should include data from the Description field in Table 1.

                       Both tables are linked by the ObjectID.

                       And then I need to have it mark the record in Table 1 as "Processed" as you suggest so it isn't generated again. I created a field called Processed for this.

                       If you can help me with this wizardry or point me to an example I would really appreciate it.


                  • 6. Re: Auto Generate Records Based on Field Calculation

                         Here's one method. There are other options possible:

                         Go to Record/Request/Page [first]
                            Set Variable [$ObjectID ; value: Table1::ObjectID ]
                            Go to Layout ["Table2" ; (Table2) ]
                            New Record/Request
                            Set Field [Table2::ObjectID ; $ObjectID ]
                            Go to Layout ["Table1" (Table1)]
                            Go to Record/Request/Page [Next ; Exit after last ]
                         End Loop

                         If you need to move more data from table 1 to table 2, you can add additional pairs of set variable and set field steps.

                    • 7. Re: Auto Generate Records Based on Field Calculation

                           I'm having trouble getting the Set Field to match what you've written. I specify target field as Table2""Object ID. Where should the $ObjectID be input? Trying to put it into the second specify button (next to repetition) or clicking the Calculated result Specify button both don't seem to work. I keep getting the message "A number, text constant, field name or "(" is expected here. And it places the insertion point right after the Table name, before ::ObjectID.

                           For moving additional data, should the added pairs of variable and set fields be input after the first Set Field line, or after the second Go To Layout?

                      • 8. Re: Auto Generate Records Based on Field Calculation

                             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 box. 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.

                             Also, I left out a line of the script.

                             Inside the loop, you'd also want to add:

                             Set Field [Table1::Processed ; True ]

                             Just before the Go To Record step.

                             This would be the step to "mark" the record so that this script does not repeat this process on the same record the next time that it executes.

                        • 9. Re: Auto Generate Records Based on Field Calculation

                               Thanks for all your help. I'm making progress. It is now creating the new records, and it correctly marks the records in table 1 as processed. However, the new records it creates are not getting the ObjectID input.

                               In the Set Variable dialog box I have:

                               Name $ObjectID

                               Value Table1::ObjectID

                               Repetition: 1

                               Then in Set Field it is:

                               Set Field [Table2::ObjectID; "$ObjectID")

                               Any idea what could be wrong?

                          • 10. Re: Auto Generate Records Based on Field Calculation

                                 I figured out the issue. I had the wrong field in the Set Field. This is great. Thanks for all the help Phil. You have been an amazing resource while developing this database.