AnsweredAssumed Answered

Import 1 text file into 2 tables

Question asked by SusanCane on Jan 20, 2014
Latest reply on Jan 20, 2014 by philmodjunk


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.