1 Reply Latest reply on Jan 20, 2014 3:26 PM by philmodjunk

    Import 1 text file into 2 tables



      Import 1 text file into 2 tables



           Fairly new to FM so please bear with me.

           I have created a script that imports a text file into two tables.  The two tables are related using a field called "worksorderno"

           WODatalist (Parent worksorderno)

           WODetail (Related Child worksorderno)

           The WODatalist has the worksorderno set to unique but the relating records in WODetail have no unique identifier.

           My script works fine if a user imports the text file once.  BUT occasionally the WODetail records could be amended and the text file will need to be reimported.

           As I have set up the unique value for worksorderno in the WODatalist the record is not reimported here but the "child" records are reimported into the WODetail.  

           Below is the script I am using.... what I need guidance on is how to first check if there are child records in WODetail and if there are delete them. Then run the script as normal.

      Go to Layout [ “Select Chain” (chains) ]

      Set Variable [ $datapath; Value:chains::datapath ]

      Set Variable [ $chainid; Value:chains::chainid ]

      Set Variable [ $AccName ; Value:Get ( AccountName ) ]

      Set Error Capture [ On ]

      Go to Layout [ “Data Files” (datafiles) ]

      Perform Find [ Specified Find Requests: Find Records ; Criteria: datafiles::chainid : “== $chainid” ]

           [ Restore ]

      If [ Get(FoundCount) 0 ]

      Delete All Records

           [ No dialog ]

      End If

      Import Records [ Folder Name: $datapath; File Type: Text files ; Target: “datafiles” ; Method: Add; Character Set: “UTF-8”; Field


           Source field 3 import to datafiles::filepath ]

           [ No dialog; Data contains column names ]

      Go to Record/Request/Page

           [ First ]

      If [ Get ( FoundCount ) 0 ]


      Set Field [ datafiles::chainid ; $chainid ]

      Go to Record/Request/Page

           [ Next; Exit after last ]

      End Loop

      Go to Layout [ “Data Files” (datafiles) ]

      Pause/Resume Script [ Indefinitely ]

      Go to Record/Request/Page

           [ First ]


      If [ datafiles::chainid = $chainid ]

      Set Variable [ $filepath ; Value:Substitute ( datafiles::filepath ; "file://" ; "filewin:/" ) ]

      Go to Layout [ “Work Orders List” (wodatalist) ]

      Import Records [ Source: “$filepath” ; Target: “wodatalist” ; Method: Add; Character Set: “UTF-8”; Field Mapping:

           Source field 1 import to wodatalist::chainid

           Source field 2 import to wodatalist::filename

           Source field 3 import to wodatalist::worksorderno

           Source field 4 import to wodatalist::Qty Ordered

           Source field 5 import to wodatalist::Set1

           Source field 6 import to wodatalist::Set2

           Source field 7 import to wodatalist::Set3 ]

           [ No dialog ]

      Import Records [ Source: “$filepath” ; Target: “wodetail”; Method: Add; Character Set: “UTF-8”; Field Mapping: Source

           field 1 import to wodetail::chainid

           Source field 2 import to wodetail::filename

           Source field 3 import to wodetail::worksorderno

           Source field 4 import to wodetail::Qty Ordered

           Source field 5 import to wodetail::Set1

           Source field 6 import to wodetail::Set2

           Source field 7 import to wodetail::Set3

           Source field 8 import to wodetail::Set4

           Source field 9 import to wodetail::Set5

           Source field 10 import to wodetail::Set6

           Source field 11 import to wodetail::Set7

           Source field 12 import to wodetail::Set8

           Source field 13 import to wodetail::Set9

           Source field 14 import to wodetail::Set10

           Source field 15 import to wodetail::Set11

           Source field 16 import to wodetail::Set12

           Source field 17 import to wodetail::Set13

           Source field 18 import to wodetail::Set14

           Source field 19 import to wodetail::Set15

           Source field 20 import to wodetail::Set16

           Source field 21 import to wodetail::Set17

           Source field 22 import to wodetail::Set18

           Source field 23 import to wodetail::Set19

           Source field 24 import to wodetail::Set20

           Source field 25 import to wodetail::field1

           Source field 26 import to wodetail::field2

           Source field 27 import to wodetail::field3

           Source field 28 import to wodetail::field4

           Source field 29 import to wodetail::field5

           Source field 30 import to wodetail::field6

           Source field 31 import to wodetail::field7

           Source field 32 import to wodetail::field8

           Source field 33 import to wodetail::field9

           Source field 34 import to wodetail::field10

           Source field 35 import to wodetail::field11

           Source field 36 import to wodetail::field12

           Source field 37 import to wodetail::field13

           Source field 38 import to wodetail::field14

           Source field 39 import to wodetail::field15

           Source field 40 import to wodetail::field16

           Source field 41 import to wodetail::field17

           Source field 42 import to wodetail::field18

           Source field 43 import to wodetail::field19

           Source field 44 import to wodetail::field20

           Source field 45 import to wodetail::field21

           Source field 46 import to wodetail::field22

           Source field 47 import to wodetail::field23

           Source field 48 import to wodetail::field24

           Source field 49 import to wodetail::field25

           Source field 50 import to wodetail::field26

           Source field 51 import to wodetail::field27

           Source field 52 import to wodetail::field28

           Source field 53 import to wodetail::field29

           Source field 54 import to wodetail::field30

           Source field 55 import to wodetail::field31

           Source field 56 import to wodetail::field32

           Source field 57 import to wodetail::field33

           Source field 58 import to wodetail::field34

           Source field 59 import to wodetail::field35

           Source field 60 import to wodetail::field36

           Source field 61 import to wodetail::field37

           Source field 62 import to wodetail::field38

           Source field 63 import to wodetail::field39

           Source field 64 import to wodetail::field40

           Source field 65 import to wodetail::field41

           Source field 66 import to wodetail::field42

           Source field 67 import to wodetail::field43

           Source field 68 import to wodetail::field44

           Source field 69 import to wodetail::field45

           Source field 70 import to wodetail::field46

           Source field 71 import to wodetail::field47

           Source field 72 import to wodetail::field48

           Source field 73 import to wodetail::field49

           Source field 74 import to wodetail::field50

           Source field 75 import to wodetail::field51

           Source field 76 import to wodetail::field52

           Source field 77 import to wodetail::field53

           Source field 78 import to wodetail::field54

           Source field 79 import to wodetail::field55

           Source field 80 import to wodetail::field56

           Source field 97 import to wodetail::field73

           Source field 98 import to wodetail::field74

           Source field 99 import to wodetail::field75

           Source field 100 import to wodetail::field76

           Source field 101 import to wodetail::field77

           Source field 102 import to wodetail::field78

           Source field 103 import to wodetail::field79

           Source field 104 import to wodetail::field80

           Source field 105 import to wodetail::field81

           Source field 106 import to wodetail::field82

           Source field 107 import to wodetail::field83

           Source field 108 import to wodetail::field84

           Source field 109 import to wodetail::field85

           Source field 110 import to wodetail::field86

           Source field 111 import to wodetail::field87

           Source field 112 import to wodetail::field88

           Source field 113 import to wodetail::field89

           Source field 114 import to wodetail::field90

           Source field 115 import to wodetail::field91

           Source field 116 import to wodetail::field92

           Source field 117 import to wodetail::field93

           Source field 118 import to wodetail::field94

           Source field 119 import to wodetail::field95

           Source field 120 import to wodetail::field96

           Source field 121 import to wodetail::field97

           Source field 122 import to wodetail::field98

           Source field 123 import to wodetail::field99

            [ ]

      If [ Get ( FoundCount ) 0 ]

      Delete All Records

           [ No dialog ]

      End If

      Go to Layout [ “importlist” (importlist) ]

      Delete All Records

           [ No dialog ]

      Go to Record/Request/Page

           [ Next; Exit after last ]

      End Loop

      Go to Layout [ “Data Files” (datafiles) ]

      Go to Record/Request/Page

           [ Next; Exit after last ]

      End Loop

      End If

      Go to Layout [ “Work Orders List” (wodatalist) ]

      Perform Find [ Specified Find Requests: Find Records ; Criteria: wodatalist::chainid : “$chainid” ]

           [ Restore ]

      Send Event [ open document/application ; File Name: “filewin:/$datapath/complete.bat” ]


           Any guidance would be appreciated.


           Thank you.



        • 1. Re: Import 1 text file into 2 tables

               There is a lot in that script that you have not explained. I'm seeing references to a number of different table occurrences other than the two you list at the start of your post. You'll need to explain that part of your script and the tables and relationships being accessed by that part in much more detail.