Code for Finish.Do for Add new items

[mzwai]

I am busy creating a wizard that will import the data from a tab delimited file. I am battling with the Finish. Do part of it and have tried several code solutions but have no success. Can someone please help me in this last step. I amusing AM 5.10
I have attached the text file and my wizard code is:

Name = "Wizard"
Title = "Import Assets"
Version = "6441"
{ String strOrigFile
  Value = ""
}
{ String strValidatedAssetData
  Value = ""
}
{ String strAssetDataValid
  Value = RetVal="false"
}
{ String strFileContents
  Value = ""
}
{ Page pgFileSelect
  Title = "Select File containing Assets"
  { FileEdit FILEEDIT1
    Label = "Select the text file (.txt) containing Asset Data to import"
    Mandatory = 1
    MultiLine = 0
    Value = ""
  }
  { Transition TRANSITION1
    Condition = 1
    { Do = 
      Dim theFile As String
      Dim theLine As String
      Dim lErr As Long
      
      ' Read from file
      Open {pgFileSelect.FILEEDIT1.Value} For Input As #1
      if NOT EOF(1) then
        'Line Input #1, theLine
      end if
      
      Do While Not EOF(1)
        'Line Input #1, theLine
        theFile = theFile + Input(1000, #1)
        'theFile = theFile + theLine
      Loop
      Close #1
      
      Dim MyStr
        MyStr=theFile
        MyStr=Replace(MyStr, chr(9), "",0)
        MyStr=Replace(MyStr, chr(14), "",0)
        'MyStr=Replace(MyStr, chr(13), "",0)
      
      lErr = amSetProperty("strOrigFile.VALUE", MyStr)
      
        MyStr=Replace(MyStr, "^", ",",0)
        MyStr=Replace(MyStr, "~", "|",0)
      
      lErr = amSetProperty("strFileContents.VALUE", MyStr)
      RetVal=0
    }
    To = "pgFileDisplay"
  }
}
{ Page pgFileDisplay
  Title = "Display Assets in File"
  { Label LABEL1
    { Caption = 
      'Dim theFile As String
      
      ' Read from file
      'Dim strText As String
      'Open {pgFileSelect.FILEEDIT1.Value} For Input As #1
      'Do While Not EOF(1)
      '  theFile = theFile + Input(1000, #1)
      'Loop
      'Close #1
      
      RetVal = {strOrigFile}
    }
    Visible = 1
  }
  { ListBox LISTBOX1
    ColTitle = "AssetTag|SerialNo|Model|Location|CostCenter|Supervisor|User|Assignment|A/C_Code|New/Used|Supplier|InvoiceNo|InvoiceDate|Purchase|StartDate|DeprType|Budget|PlannedRetirementDate|FixedAssetNo"
    ColWidth = "5|5|5|5|6|6|3|6|5|5|5|6|5|5|5|5|4|6|5"
    Editable = ""
    ReadOnly = 1
    Value = ""
    Values = RetVal={strFileContents}
  }
  { Transition TRANSITION1
    { Do = 
      Dim lErr As Long
      Dim workingString,validatedString As String
      Dim lHr As Long
      Dim lRc As long
      Dim count,i As Integer
      
      workingString={strOrigFile}
      lErr=AmSetProperty("strAssetDataValid.VALUE","true")
      
      count = CountValues(workingString, "^", "")
      Dim cpy As Long
      Dim cont As Long
      Dim aline As String
      For i=2 to count
        aline=GetListItem(workingString,"^",i,"")
        ' AssetTag Column
        validatedString=validatedString+GetListItem(aline,"~",1,"")+"|"
      
        ' Serial No Column
        lHr = AmDbGetPk("amAsset", "AssetTag='"+GetListItem(aline,"~",2,"")+"'")
        if lHr>0 then
          lErr=AmSetProperty("strAssetDataValid.VALUE","false")
          validatedString=validatedString+"ERROR: Asset Exists"+"|"
        else
          validatedString=validatedString+GetListItem(aline,"~",2,"")+"|"
        end if
      
        ' Model Column
        lHr = AmDbGetPk("amModel", "BarCode='"+GetListItem(aline,"~",3,"")+"'")
        if lHr>0 then
          validatedString=validatedString+GetListItem(aline,"~",3,"")+"|"
        else
          lErr=AmSetProperty("strAssetDataValid.VALUE","false")
          validatedString=validatedString+"ERROR: Model Does Not Exist"+"|"
        end if
      
        ' Location Column
        lHr = AmDbGetPk("amLocation", "BarCode='"+GetListItem(aline,"~",4,"")+"'")
        if lHr>0 then
          validatedString=validatedString+GetListItem(aline,"~",4,"")+"|"
        else
          lErr=AmSetProperty("strAssetDataValid.VALUE","false")
          validatedString=validatedString+"ERROR: Location Does Not Exist"+"|"
        end if
      
        ' CostCenter Column
        lHr = AmDbGetPk("amCostCenter", "Title='"+GetListItem(aline,"~",5,"")+"'")
        if lHr>0 then
          validatedString=validatedString+GetListItem(aline,"~",5,"")+"|"
        else
          lErr=AmSetProperty("strAssetDataValid.VALUE","false")
          validatedString=validatedString+"ERROR: Cost Center Does Not Exist"+"|"
        end if
      
        ' Supervisor Column
        Dim names As String
        names = GetListItem(aline,"~",6,"")
        lHr = AmDbGetPk("amEmplDept","Name='"+GetListItem(names," ",1,"")+"' And FirstName='"+GetListItem(names," ",2,"")+"'")
        if lHr>0 then
          validatedString=validatedString+GetListItem(aline,"~",6,"")+"|"
       else
          lErr=AmSetProperty("strAssetDataValid.VALUE","false")
          validatedString=validatedString+"ERROR: Supervisor Does Not Exist"+"|"
        end if
      
        ' User Column
        lHr = AmDbGetPk("amEmplDept","Name='"+GetListItem(aline,"~",7,"")+"'")
        if lHr>0 then
          validatedString=validatedString+GetListItem(aline,"~",7,"")+"|"
       else
          lErr=AmSetProperty("strAssetDataValid.VALUE","false")
          validatedString=validatedString+"ERROR: User Does Not Exist"+"|"
        end if
      
        ' Assignment Column
        lHr = AmDbGetPk("amAsset", "Status='"+ GetListItem(aline,"~",8,"")+"'")
        if lHr>0 then
          validatedString=validatedString+GetListItem(aline,"~",8,"")+"|"
       else
          lErr=AmSetProperty("strAssetDataValid.VALUE","false")
          validatedString=validatedString+"ERROR: Assignment Does Not Exist"+"|"
       end if
      
        ' A/C Code
        validatedString=validatedString+GetListItem(aline,"~",9,"")+"|"
      
        ' New/Used Column
        validatedString=validatedString+GetListItem(aline,"~",10,"")+"|"
      
        ' Supplier Column
          lHr = AmDbGetPk("amCompany", "Name='"+GetListItem(aline,"~",11,"")+"'")
          if lHr>0 then
            validatedString=validatedString+GetListItem(aline,"~",11,"")+"|"
          else
            lErr=AmSetProperty("strAssetDataValid.VALUE","false")
            validatedString=validatedString+"ERROR: Supplier Does Not Exist"+"|"
          end if
       
        ' Inovice_No Column
          validatedString=validatedString+GetListItem(aline,"~",12,"")+"|"
      
        ' Invoice_Date Column
        validatedString=validatedString+GetListItem(aline,"~",13,"")+"|"
      
        ' Purchase_price Column
        validatedString=validatedString+GetListItem(aline,"~",14,"")+"|"
      
        ' StartDate Column
        validatedString=validatedString+GetListItem(aline,"~",15,"")+"|"
      
        ' DeprType Column
        lHr = AmDbGetPk("amDeprScheme","Name='"+GetListItem(aline,"~",16,"")+"'")
        if lHr>0 then
          validatedString=validatedString+GetListItem(aline,"~",16,"")+"|"
       else
          lErr=AmSetProperty("strAssetDataValid.VALUE","false")
          validatedString=validatedString+"ERROR: Budget Does Not Exist"+"|"
        end if
      
        ' Budget Column
        lHr = AmDbGetPk("amCsaFinBudget","Name='"+GetListItem(aline,"~",17,"")+"'")
        if lHr>0 then
          validatedString=validatedString+GetListItem(aline,"~",17,"")+"|"
       else
          lErr=AmSetProperty("strAssetDataValid.VALUE","false")
          validatedString=validatedString+"ERROR: Budget Does Not Exist"+"|"
        end if
      
        ' Planned_Removal_Date Column
        validatedString=validatedString+GetListItem(aline,"~",18,"")+"|"
      
        ' Fixed Asset Column
        validatedString=validatedString+GetListItem(aline,"~",19,"")+","
      
       'lHr = AmDbGetPk("amNature","Name='"+GetListItem(aline,"~",20,"")+"'")
       ' if lHr>0 then
         ' validatedString=validatedString+GetListItem(aline,"~",20,"")+","
       'else
         ' lErr=AmSetProperty("strAssetDataValid.VALUE","false")
         ' validatedString=validatedString+"ERROR: User Does Not Exist"+","
        'end if
      
      Next
      
      lErr = AmSetProperty("pgFileValidate.LISTBOX1.VALUES",validatedString)
      
      if {strAssetDataValid}="true" then
        lErr=AmSetProperty("strValidatedAssetData.VALUE",validatedString)
      end if
    }
    To = "pgFileValidate"
  }
}
{ Page pgFileValidate
  Title = "Validate Asset Data"
  { Label LABEL1
    { Caption = 
      'Dim theFile As String
      
      ' Read from file
      'Dim strText As String
      'Open {pgFileSelect.FILEEDIT1.Value} For Input As #1
      'Do While Not EOF(1)
      '  theFile = theFile + Input(1000, #1)
      'Loop
      'Close #1
      
      RetVal = {strOrigFile}
    }
    Visible = 1
  }
  { ListBox LISTBOX1
    ColTitle = "AssetTag|SerialNo|Model|Location|CostCenter|Supervisor|User|Assignment|A/C_Code|New/Used|Supplier|InvoiceNo|InvoiceDate|Purchase|StartDate|DeprType|Budget|PlannedRetirementDate|FixedAssetNo"
    ReadOnly = 1
    Value = ""
    Values = RetVal={strFileContents}
  }
}
{ Finish FINISH
  { Condition = 
    if {strAssetDataValid}="true" then
      RetVal=1
    else
      RetVal=0
    end if
  }
  { Do = 
    Dim lErr As Long
    Dim lRc, lHr As Long
    Dim workingString As String
    Dim aline As String
    Dim count,i As Integer
    
    'workingString={strValidatedAssetData}
    lErr=AmLog(workingString)
    
    count = CountValues(workingString, "^", "")
    For i=1 to count
      aline=GetListItem(workingString,"^",i,"")
      ' AssetTag Column
      'validatedString=validatedString+GetListItem(aline,"~",1,"")+"|"
      ' SerialNo Column
      'validatedString=validatedString+GetListItem(aline,"~",2,"")+"|"
      ' Model Column
      'validatedString=validatedString+GetListItem(aline,"~",3,"")+"|"
      ' Location
      'validatedString=validatedString+GetListItem(aline,"~",4,"")+"|"
      ' CostCenter Column
      'validatedString=validatedString+GetListItem(aline,"~",5,"")+"|"
      ' SuperVisor Column
      'validatedString=validatedString+GetListItem(aline,"~",6,"")+"|"
      ' User Column
      'validatedString=validatedString+GetListItem(aline,"~",7,"")+"|"
      ' Assignment Column
      'validatedString=validatedString+GetListItem(aline,"~",8,"")+"|"
      ' A/C Code Column
      'validatedString=validatedString+GetListItem(aline,"~",9,"")+"|"
      ' new/used Column
      'validatedString=validatedString+GetListItem(aline,"~",10,"")+"|"
      ' Supplier Column
      'validatedString=validatedString+GetListItem(aline,"~",11,"")+"|"
      ' Invoice_No Column
      'validatedString=validatedString+GetListItem(aline,"~",12,"")+"|"
      ' Invoice_Date Column
      'validatedString=validatedString+GetListItem(aline,"~",13,"")+"|"
      ' Purchase_Price Column
      'validatedString=validatedString+GetListItem(aline,"~",14,"")+"|"
      ' Start_Date Column
      'validatedString=validatedString+GetListItem(aline,"~",15,"")+"|"
      ' Depr Type Column
      'validatedString=validatedString+GetListItem(aline,"~",16,"")+"|"
      ' Budget Column
      'validatedString=validatedString+GetListItem(aline,"~",17,"")+"|"
      ' Planned_Removal_Date Column
      'validatedString=validatedString+GetListItem(aline,"~",18,"")+"|"
    ' Fixed_Asset Column
      'validatedString=validatedString+GetListItem(aline,"~",19,"")+","
      'lErr = AMProgress(i/count*100)
    Next
    'For i = 1 To count
    'count = CountValues([CurrentSelection],", ","")
    'For i = 1 To count
    
    
     lHr = amCreateRecord("amPortfolio")
     lErr = amSetFieldStrValue(lHr, "Model.Barcode", GetListItem(aline,"~",3,""))
     'lErr= amSetFieldStrValue(lHr, "AssetTag", GetListItem(aline,"~",1,""))
     
    lErr = amInsertRecord(lHr)
  }
  { Label = 
    'if {strAssetDataValid}="true" then
    '  RetVal=1
    'else
    '  RetVal=0
    'end if
  }
  Summary = 1
}

[Artur]

Hi,

I have no AC available so I can not test it.
Write at least what errors you get.

regards
Artur

[mzwai]

Thanks for replying .
The problem error i am getting is in my Create Record statement.
When running it I get the error message
2009/02/12 13:48:28.000 1 1 [Thrd#:6220](0) Field 'Name (Name)' in table 'Models (amModel)' cannot be empty. ('Line 62 of script 'FINISH.Do, line 282'')
2009/02/12 13:48:28.000 1 1 [Thrd#:6220](0) Field 'Nature (Nature)' in table 'Models (amModel)' cannot be empty. ('Line 62 of script 'FINISH.Do, line 282'')
2009/02/12 13:48:28.000 1 1 [Thrd#:6220](0) Unknown error; rc=-2009 ('Line 62 of script 'FINISH.Do, line 282'')
2009/02/12 13:48:28.000 0 1 [Thrd#:6220](0) Wizard failed to execute

The

[mzwai]

Thanks for replying. The error message I get.

2009/02/12 13:48:28.000 1 1 [Thrd#:6220](0) Field 'Name (Name)' in table 'Models (amModel)' cannot be empty. ('Line 62 of script 'FINISH.Do, line 282'')
2009/02/12 13:48:28.000 1 1 [Thrd#:6220](0) Field 'Nature (Nature)' in table 'Models (amModel)' cannot be empty. ('Line 62 of script 'FINISH.Do, line 282'')
2009/02/12 13:48:28.000 1 1 [Thrd#:6220](0) Unknown error; rc=-2009 ('Line 62 of script 'FINISH.Do, line 282'')
2009/02/12 13:48:28.000 0 1 [Thrd#:6220](0) Wizard failed to execute

[mzwai]

I have looked through some system examples and the documentation but i get stuck in knowing how to set the values in another table that need to refernce the same Portoflio item
. For instance the Serial field is in the amAsset table but for the amSetFiledStrValue only works for fields in the current table or that have reference key.

So far i have(a snippet)
lHr = amCreateRecord("amPortfolio")

lErr= amSetFieldStrValue(lHr, "AssetTag", GetListItem(aline,"~",1,""))
lErr= amSetFieldStrValue(lHr, "SerilaNo", GetListItem(aline,"~",2,"")) //This line needs to insert SerialNo field value in the amAsset table
lErr =amSetFieldStrValue(lHr, "Model.Barcode", GetListItem(aline,"~",3,""))
lErr =amSetFieldStrValue(lHr, "Model.lModelId","")
lErr =amSetFieldStrValue(lHr, "Location.lLocaId",GetListItem(aline,"~",4,"") )
lErr =amSetFieldStrValue(lHr, "CostCenter.lCostId",GetListItem(aline,"~",5," "))
lErr =amSetFieldStrValue(lHr, "Supervisor.lSupervId",GetListItem(aline,"~",6,"") )

[Artur]

Hi,

how did you create his script? I usually recommend "a small steps" approach:
1. define your problem
2. divide it in small pieces
3. start to write the program with most important part and add, step by step - new
part ONLY when they work.


If I were you, I would look for some existing serialno , lcation, costcenter supervisor and wrote this way:
lErr= amSetFieldStrValue(lHr, "SerilaNo", "120404")
lErr =amSetFieldStrValue(lHr, "Model.Barcode", "1212")

just to avoid problems.

AFAIR - for setting the vaues in joined tables you just use notation:
table.table.field, like you just did:
lErr =amSetFieldStrValue(lHr, "Model.Barcode", GetListItem(aline,"~",3,""))
keep in mind that you have to use amSetFieldLongValue etc, for different types.

I think the problem is here:
lErr =amSetFieldStrValue(lHr, "Model.lModelId","")

You are not passing the modelId. This way AC thinks it is a new record for model and
requires obligfatory fields for a new record.
For test do as I wrote above:
lErr =amSetFieldStrValue(lHr, "Model.lModelId","223333")

regards
Artur