1 2 Previous Next 16 Replies Latest reply on May 17, 2016 8:58 AM by Ben_B94

    Script Help

    Ben_B94

      I'll try my best to explain....

       

      Locations -----< Hire_Join >------- Hire_Import

       

      Hire_join contains HireJ_ID, Location_fk, Hire_fk

      The hire_join layout contains related fields for Location::siteName and Hire::siteName so on the Hire_Join layout they should more or less match.

       

      A script to create a new record for every location on the Hire_Join layout.

      Whilst creating the record get the Location::siteName and check it against a list of hire::siteNames. Do a text phrase match, so say Location siteName = Summer Endowed School and the Hire::SiteName = Summer School, they should match.

       

      If they match then set the foreign_key of Hire(Hire_fk) in the Hire_Join table = the ID in Hire_Import showing that matching location, basically so they will be related.

       

      My goal is to basically automate the process of importing records and relating them to locations.

      And if possible have it as a server scheduled script.

       

      script.jpg

       

      layout.jpg

      Currently if will create the records for each location but won't match up the site names.

        • 1. Re: Script Help
          erolst

          Off the top of my head …

           

          1. Create a relationship where

           

          Join::locationImport = Location_forJoin_byName::name

           

          2. After the import, do a Replace Field Contents for id_location as

           

          Location_forJoin_byName::id


          3. Open a new window (you're manipulating the found set; this way you'll always have the original window as a fallback), and perform a Constrain where id_location is empty


          If the found set is 0, you're done; all imported records received an id_location from the Locations table via the text match lookup (which implies an full match).


          If there is a found set, continue like this:


          4. Set two variables: $listOfLocationNames, and $listOfLocationIDs; easiest via ExecuteSQL() as SELECT name FROM Locations, and SELECT id FROM Locations


          5. Loop through the found set: in each record, perform this calculation:


          ValueCount (

            Left (

              $listOfLocationNames ;

              Position ( $listOfLocationNames ; Join::locationImport ; 1 ; 1 )

            )

          )


          If this returns a value ≠ 0, use GetValue() to read the corresponding ID from $listOfLocationIDs and set it into id_location.


          You may also want to do a

          PatternCount ( $listOfLocationNames ; Join::locationImport )

          to see how reliable that match was – and in general, add a flag field that tells you this record should be looked at afterwards. (If you put the result of PatternCount() in it, you can see how many potential matches there have been …)


          6. If there was a match, omit the record; else, add the location to a variable, like $missingLocations = List ( $missingLocations ; Join::locationImport )


          7. If after the loop that variable is not empty (or there still is a found set – same thing) …


          8. … go to the Locations table, use a loop to process $missingLocations and create new records (one per line).


          9. Return to the Join/Import layout and do another Replace Field Contents via the name-based relationship.


          All that's left is to inspect the records that are flagged as requiring … well, inspection. ;-) i.e. return to the original window and do a constrain on flagField > 0.


          PS: When you post a script, please try to post it as text, not as a screenshot; printing to PDF and copying the text from the resulting PDF document should work.

          • 2. Re: Script Help
            Ben_B94

            I think my main problem lies with needing the ID of $currValue which is based off $hirelist which is a list of $hireSite which is the Hire_Import::SiteName field and I don't know how to tell it to get it.

             

            Once pattern count is true so the search string exists I need the ID of that $currValue

            • 3. Re: Script Help
              erolst

              Ben_B94 wrote:

               

              I think my main problem lies with needing the ID of $currValue which is based off $hirelist which is a list of $hireSite which is the Hire_Import::SiteName field and I don't know how to tell it to get it.

               

              Once pattern count is true so the search string exists I need the ID of that $currValue

               

              My suggestion starts right after you've performed the import and is supposed to be a complete solution, so you can scrap all these other variables and stuff.

               

              You can read the post it in sequence and more or less translate it one-to-one into a script.

              • 4. Re: Script Help
                Ben_B94

                Hi Erolst, I will try to implement your method. I will have some questions and would appreciate any help given.

                 

                Location_forJoin_byName::name


                Location_forJoin_byName::id


                Are these 2 fields referencing join table fields?

                • 5. Re: Script Help
                  erolst

                  No, these are fields from the Location table.

                  • 6. Re: Script Help
                    Ben_B94

                    Hi, could you please give further clarification,

                     

                    I have TO's Location, Hire Join, Hire, with ID's

                    Location- Location ID

                    Hire Join - HJ_ID, Location ID, HireID

                    Hire - HireID

                     

                    Which fields do these reference:

                    Location_forJoin_byName::name,  is it Location Name?

                    Location_forJoin_byName::id, is it Location ID?

                     

                    Join::locationImport = Location_forJoin_byName::name is this the foreign key for location in Hirejoin? = Location::name?

                     

                    do a Replace Field Contents for id_location as

                    Location_forJoin_byName::id - not sure what field you are referencing here.

                     

                    Thanks in advance, I appreciate the help

                    • 7. Re: Script Help
                      erolst

                      You need to create a new TO of Location – Location_forJoin_byName – and relate it to the join table via

                       

                      HireJoin::locationImport = Location_forJoin_byName::name

                       

                      Now you can reference Location_forJoin_byName::id

                      • 8. Re: Script Help
                        Ben_B94

                        Does Hirejoin::locationimport just mean the foreign key field for hire in the hire-join table?

                        • 9. Re: Script Help
                          erolst

                          No, it means the field into which you import the location name, so you can match against / compare to the name field in the Location table.

                           

                          So, in a way it is a foreign key, but only on a utility / temporary basis – the goal here is to bring in the primary key from the Locations table into the real foreign key field in HireJoin.

                          • 10. Re: Script Help
                            Ben_B94

                            use GetValue() to read the corresponding ID from $listOfLocationIDs and set it into id_location.

                             

                            How do I get the ID from a variable using get Value? getvalue ( $listoflocationIDs : whatvaluenumber?)

                            • 11. Re: Script Help
                              erolst

                              Ben_B94 wrote:

                               

                              use GetValue() to read the corresponding ID from $listOfLocationIDs and set it into id_location.

                               

                              How do I get the ID from a variable using get Value? getvalue ( $listoflocationIDs : whatvaluenumber?)

                              If

                               

                              ValueCount (

                                Left (

                                  $listOfLocationNames ;

                                  Position ( $listOfLocationNames ; Join::locationImport ; 1 ; 1 )

                                )

                              )

                               

                              yields a result, then that result is the second argument for GetValue, because it is the line where the first (or only) match for location name was found.

                               

                              Consider

                               

                              Let ( [

                               

                                listOfLocationNames = "Boston¶NYC¶SanFrancisco¶Chicago¶Salem" ;

                                listOfLocationIDs = "23¶34¶45¶56¶67" ;

                               

                                positionOfFirstMatch = Position ( listOfLocationNames ; "Chicago" ; 1 ; 1 ) ; // = 25

                               

                                textUntilFirstMatch = Left ( listOfLocationNames ; positionOfFirstMatch ) ; // = "Boston¶NYC¶SanFrancisco¶C"

                               

                                numberOfLinesUntilMatch = ValueCount ( textUntilFirstMatch ) // = 4

                               

                                ] ;

                               

                                GetValue ( listOfLocationIDs ; numberOfLinesUntilMatch ) // 4th element in list of IDs – 56

                               

                              )

                              • 12. Re: Script Help
                                Ben_B94

                                //Import from the excel sheet and give the import records ID's, primary key Hire_ID

                                Perform Script [ “Hire Items” ]

                                Go to Layout [ “Hire Import” (Hire Import) ]

                                Perform Find [ Specified Find Requests: Find Records ; Criteria: Hire Import::Hire_ID : “=” ] [ Restore ]

                                Replace Field Contents [ Hire Import::Hire_ID ; Replace with serial numbers: Entry option values ; Initial value: 141; Increment value: 1 ] [ No dialog; Update Entry Options ]

                                 

                                //tried following your steps from here, copied location table, made a new window

                                Replace Field Contents [ Locations::Location_ID ; Replace with calculation: Locations 2::Location_ID ]

                                [ No dialog ]

                                New Window [ Style: Document ; Name: "Found Set "; Height: 250; Width: 150; Top: 100; Left: 100; Close: Yes ; Minimize: Yes ; Maximize: Yes ; Zoom Control Area: Yes ; Resize: Yes ]

                                If [ Get ( FoundCount ) = 0 ]

                                Exit Script [ ]

                                Else

                                Set Variable [ $listofLocationNames ; Value:ExecuteSQL ( "SELECT \"Site_Name\" from Locations = ?" ; "" ; "" ) ]

                                Set Variable [ $listofLocationIDs ; Value:ExecuteSQL ( "SELECT \"Location_ID\" from Locations = ?" ; "" ; "") ]

                                Set Variable [ $counter ; Value:1 ]

                                Go to Record/Request/Page [ First ]

                                 

                                Loop

                                Set Variable [ $varCount ; Value:ValueCount ( Left ( $listofLocationNames ; Position ( $listofLocationNames ; Hire Import::Site Name ; 1 ; 1 ) )) ]

                                If [ $varCount = 0 ]

                                Go to Record/Request/Page [ Next; Exit after last ]

                                Set Variable [ $counter ; Value:$counter + 1 ]

                                Else

                                Set Variable [ $currID; Value:GetValue ( $listofLocationIDs ; $varCount ) ]

                                Set Field [ Locations::Location_ID ; $currID ]

                                Set Variable [ $counter ; Value:$counter + 1 ]

                                End If

                                Exit Loop If [ $counter > Get ( FoundCount ) ]

                                End Loop

                                End If

                                 

                                It doesn't work but am i on the right track here? It comes up saying can't find any records matching criteria Hire_ID '='

                                but I don't get why, when you import the data from the excel sheet it wipes all the Hire_ID's so how would it not find empty ones?

                                • 13. Re: Script Help
                                  erolst

                                  Ben_B94 wrote:

                                   

                                  //Import from the excel sheet and give the import records ID's, primary key Hire_ID

                                  Perform Script [ “Hire Items” ]

                                  Go to Layout [ “Hire Import” (Hire Import) ]

                                  Perform Find [ Specified Find Requests: Find Records ; Criteria: Hire Import::Hire_ID : “=” ] [ Restore ]

                                  Replace Field Contents [ Hire Import::Hire_ID ; Replace with serial numbers: Entry option values ; Initial value: 141; Increment value: 1 ] [ No dialog; Update Entry Options ]

                                  Ben_B94 wrote:

                                  It doesn't work but am i on the right track here? It comes up saying can't find any records matching criteria Hire_ID '='

                                  but I don't get why, when you import the data from the excel sheet it wipes all the Hire_ID's so how would it not find empty ones

                                   

                                  Not sure why you're doing this.

                                   

                                  1. After a successful import, the new found set comprises the imported records, and those are the ones with an empty primary key.

                                   

                                  2. Why not just just use a normal auto-enter ID that is set automatically on record creation? You will create records in this join table by other means, so why use two mechanisms, instead of just one (and the most convenient one, at that)?

                                   

                                  As for the rest of the script: I suggest you make sure the import works, then forget about that part for the time being.

                                   

                                  Go to your join table and create a few sample import records and apply your script (sans import) to that sample set. See what happens if the location exists / doesn't exist in the Locations table.

                                   

                                  and so on … if you can decouple scripts (or part of scripts) from each other that's good because that allows you to test your components independently.

                                   

                                  Oh yes; the last part

                                   

                                  Ben_B94 wrote:

                                   

                                  Loop

                                  Set Variable [ $varCount ; Value:ValueCount ( Left ( $listofLocationNames ; Position ( $listofLocationNames ; Hire Import::Site Name ; 1 ; 1 ) )) ]

                                  If [ $varCount = 0 ]

                                  Go to Record/Request/Page [ Next; Exit after last ]

                                  Set Variable [ $counter ; Value:$counter + 1 ]

                                  Else

                                  Set Variable [ $currID; Value:GetValue ( $listofLocationIDs ; $varCount ) ]

                                  Set Field [ Locations::Location_ID ; $currID ]

                                  Set Variable [ $counter ; Value:$counter + 1 ]

                                  End If

                                  Exit Loop If [ $counter > Get ( FoundCount ) ]

                                  End Loop

                                  End If

                                   

                                  is much simpler if you reverse the logic (and use more meaningful $var names): write an ID if you can get one, but progress to the next record in either case:

                                   

                                  Loop

                                    Set Variable [ $indexPositionOfLocation ; Value:ValueCount ( Left ( $listofLocationNames ; Position ( $listofLocationNames ; Hire Import::Site Name ; 1 ; 1 ) )) ]

                                    If [ $indexPositionOfLocation // i.e. ≠ 0 ]

                                      Set Field [ Locations::Location_ID ; GetValue ( $listofLocationIDs ; $indexPositionOfLocation ) ]

                                    End If

                                    Go to Record/Request/Page [ Next; Exit after last ]

                                  End If

                                   

                                  And unless you want to prevent an error entry in your server log, Go to Next Record [ exit after last ] is just fine; no need to keep your private counter.

                                   

                                  And even if you want to, there is

                                   

                                  Get ( RecordNumber ) = Get ( FoundCount )

                                  • 14. Re: Script Help
                                    BruceRobertson

                                    Perform Find [ RESTORE ]

                                    Ugh.

                                    Especially when trying to get help from other people, this is a terrible feature.

                                    I always strongly suggest explicit scripted finds.

                                    Enter Find Mode [ ] //no restore

                                    Set Field [ Hire Import::Hire_ID; "="]

                                    Perform Find []

                                    1 2 Previous Next