1 2 Previous Next 18 Replies Latest reply on Aug 1, 2011 1:52 PM by mgores

    a single Set Field script for multiple table/layouts

    mgores

      Title

      a single Set Field script for multiple table/layouts

      Post

      In my file, I have multiple tables which I can create records in.  I am trying to come up with a script that will create a new record and insert an ID from another file into the fID field of the table.  I have the script set up so far that it asks the user for a job number, when the user enters that number it checks the foriegn file to make sure it is valid and gets the ID number as a variable.  Now I need to pass that variable to the new record in the current table.  I can see how to do this using a separate script for each table, but was hoping I could do it with one script for all tables.

        • 1. Re: a single Set Field script for multiple table/layouts
          philmodjunk

          Instead of Set Field, take a look at set field by name. That allows you to use a calculation to compute a text result that it uses as the tableOccurrenceName::FieldName to recieve the value from the second parameter's calculation.

          You can even pass this text as a script parameter to your script.

          • 2. Re: a single Set Field script for multiple table/layouts
            mgores

            That seems like it should work.  What I am having trouble with is setting the variable for the field name on a particular table.  If I am on the EV form I would want to set the field EV::mtID to $mt, if I am on the BP form I want to set BP::mtID to $mt, etc.

            Would I use a Get(ActiveFieldTableName) to get the table.  Then use set field by name - Get(ActiveFieldTableName)::mtID; $mt  ?

            Where $mt is the value I want entered into the current layout/form's mtID field.

            • 3. Re: a single Set Field script for multiple table/layouts
              philmodjunk

              You're close, try: Set Field By Name [ Get(ActiveFieldTableName) & "::mtID" ; $mt ]

              With set field by Name, the first parameter is now a calcluation much like the second one ($mt), but it must return text and be the valid name of a field. (a "Valid" field name must also take into account what layout is current at the time you perform this step.)

              • 4. Re: a single Set Field script for multiple table/layouts
                mgores

                Ok, so I've tried:

                Set Var $repnum to gRepnum - this is set from a dialog box

                Set Var $table; Get(ActiveFieldTableName)

                Freeze window

                Goto layout report

                new record

                set field report number to $repnum

                Set Var $mt to report::mtID - the mtID is looked up from the jobnumber if empty returns an error

                Goto [original layout]

                new record

                SetField By Name [ Get(ActiveFieldTableName) & "::mtID" ; $mt ]

                Clear [gRepnum]

                refresh window

                But the value entered into the current tables mtID filed is only the number portion of the original mtID.  i.e.  the mtID should be FA713, but I am getting 713.

                It does seem to work as the fields are populating with the right data, but I am worried that if there was a US713 and an FA713 that it may not work right.

                • 5. Re: a single Set Field script for multiple table/layouts
                  philmodjunk

                  Open Manage | Database | Fields, find the field's definition and change its type from Number to Text.

                  • 6. Re: a single Set Field script for multiple table/layouts
                    philmodjunk

                    Check the types of all the mt fields involved here.

                    • 7. Re: a single Set Field script for multiple table/layouts
                      mgores

                      Ok I even clicked on the wrong post for Best answer.  Phil's post on the "set field by name" is what should have gotten that click.

                      It works perfectly Phil.  Thanks for helping me though another one of these issues.  This will save a lot of time and eliminate the need to write a bunch of similar scripts.

                      • 8. Re: a single Set Field script for multiple table/layouts
                        mgores

                        This is strange.....now it seems to be working from the EV form/table.  In a second form/table, the script seems to work in that all of the variables are being set correctly, but  it failes to set the field.  In another the Get(ActiveFieldTableName) & "::mtID" only gets ::mtID, it doesn't get the table name.

                        The script is below.  The EV::jobnum is a global field that I am using to put into the job number field of the testreport layout- which then gets the MasterTrackID via lookup.  The Show Custom Dialogs after the first one are for debugging to see if the variables are getting set.

                        New Record/Request
                        Show Custom Dialog [ Title: "new job"; Message: "Enter the Job number"; Buttons: “OK”, “Cancel”; Input #1: EV::jobnum, "Job
                        Number" ]
                        Set Variable [ $newform; Value:EV::jobnum ]
                        Set Variable [ $formField; Value:Get ( ActiveFieldTableName ) & "::MasterTrackID" ]
                        Show Custom Dialog [ Title: "new job"; Message: $formField; Buttons: “OK”, “Cancel” ]
                        Freeze Window
                        Go to Layout [ “testreport” (testreport) ]
                        New Record/Request
                        Set Field [ testreport::Jobnumber; $newform ]
                        Set Variable [ $MT; Value:testreport::MasterTrackID ]
                        Go to Layout [ original layout ]
                        Show Custom Dialog [ Message: $formField & EV::jobnum & " - " & $mt; Buttons: “OK”, “Cancel” ]
                        Refresh Window
                        New Record/Request
                        Set Field By Name [ $formField; $mt ]
                        Show Custom Dialog [ Message: $formField & EV::jobnum & " - " & $mt; Buttons: “OK”, “Cancel” ]
                        Clear [ EV::jobnum ]

                        • 9. Re: a single Set Field script for multiple table/layouts
                          philmodjunk

                          Get(ActiveFieldTableName)

                          will only return the correct value if the cursor is in that field. You'll need to check and see if that's not the case at times when this script is performed.

                          That issue is one of the reasons why I usually pass the field and table occurence names as a parameter to the script. Then you don't have to make sure that the cursor is actually in any given field.

                          One way to do this is to put GetFieldName ( YourTableOccurrence::YourField ) in the parameter box. This expression will update with the correct text should you later rename either the table occurrence or the field so you don't have to update any part of the script to adjust correctly for the name change.

                          • 10. Re: a single Set Field script for multiple table/layouts
                            mgores

                            Would the GetFieldName work if there was no field selected?  I am running the script from a button on the form and trying to make the one script work from many different forms/tables.  So that a technician would click the button, enter the job number of job they are working on, and a new record is created with the customer/job info filled in - they can then enter the test data into portals to a data table.

                            Would it work to hide a field to the appropriate table behind the button, essentially making it the active field when the button is clicked.  That way as long as the field is from the desired layout it would get the correct table name?

                            • 11. Re: a single Set Field script for multiple table/layouts
                              philmodjunk

                              Yes, it's independent of the status of the current layout.

                              No need to have the button even present on that layout so what you describe isn't needed and I don't think you could get it to work unless you somehow scripted a go to field to get the cursor into that field. (Clicking a button in front of a field won't put the cursor into it.)

                              What is also important to keep in mind is that any script you write will always be "layout sensitive" in the sense that your current layout specifies a table occurrence and that in turn determines both the current record and what relationships link that current record to any records in related tables. Thus, these forms of indirect reference will allow one script to work with many layouts, but only if the values they return for field and table occurrence names work with the current layout's table occurrence.

                              • 12. Re: a single Set Field script for multiple table/layouts
                                mgores

                                OK, I am getting a bit more confused.  Maybe if I go back to the beginning to explain what I am trying to do.....

                                A technician will be doing a particular test, say X-ray, on several jobs.  He will go to the Xray layout (based on the Xray table), click a "New Job" button to create a new form, be prompted to enter the job number - the script will create a new record in the Xray table with the Xray::MasterTrackID set from the $mt value.  He can then enter the data as he is doing the tests.  If he fills in all of the available space on the form, there is a "New Page" button that will duplicate the record in the Xray table, giving him a new blank form with all of the job data still filled in. (the data is in a separate table filled in through portals).  Once he is done with that job, he clicks the New Job button and gets prompted for the new job number.

                                As I am writing this I think I may be making this too hard and coming at it from the wrong direction.  It might be easier to set up a "global table" with job number and MasterTrack global fields.  Enter the Job number there, get the MasterTrackID via lookup, then have buttons to go to each of the forms and setting the forms MasterTrackID to the gMasterTrackID.  But that would still require a separate script for each Form/tableUndecided

                                • 13. Re: a single Set Field script for multiple table/layouts
                                  philmodjunk

                                  Excuse, me but made a major oops there in my last post. "no need to have the button on the layout" should read "no need to have the FIELD..." Embarassed

                                  You can then use button setup to enter an expression like the example I gave to be evaluated and the result passed as a parameter to the script where Get ( ScriptParameter ) can extract it.

                                  • 14. Re: a single Set Field script for multiple table/layouts
                                    mgores

                                    OK.  How does that GetFieldName ( YourTableOccurrence::YourField ) work where it would pick up the value of the active table?  i.e. return EV from the EV layout or Xray from from the Xray layout.

                                    1 2 Previous Next