I'm new to FDM scripting. So any pointers will be appreciated. I need to import a TB with the following format in exel file.
row1 ***CostCenter Number***
row2 ***TB name*
row3 - Other parameters
row4 - Blank
row5 - Data headers
row6 - Data begins - format given below -
row7 - Account Desc Opening Bal Closing Bal
I have created an import format with the 4 fields - CostCentre, Account,Desc and closing bal. Now my question is -
1. How do i create script that picks the costcenter number from row1 ?
Thanks in Advance!!
That format will not work in an excel file, FDM requires a specific excel template, also the FDM excel templates do not use import scripts,so first convert to a csv file.
You will then need to use a temporary variable for this e.g. RES.PvarTemp1 and create 2 import scripts
Assuming that cost centre is the only value on line one of the file what you need to do is:
Attach an Import script to the Amount entry in your import format and put in the following code (this assumes cost centre is always the first header line and your script is called GetCostCentre)
If Not Len(RES.PvarTemp1) > 0 Then
RES.PvarTemp1 = DW.Utilities.fParseString(strRecord,4,1,",")
GetCostCentre = strField
Attach an import script to the CostCentre entry in the import format called SetCostCentre, it will have one line
SetCostCentre = RES.PvarTemp1
There is a very similar example in the FDM admin guide Scripting cahpter which has a bit more description.
Edited by: SH on Dec 7, 2012 11:17 AM
Thanks for the reply.
What if my entity number is in row number 2 ? I have gone through the FDM help file but struggling to get this right. I have my TB in the following csv format as shown below -
* Amounts in curr
CostCentre Name (Cost Centre Number),,,,,,,
,, Balance,,, Balance,,
Also, I'm using two scripts (one for the amount and the other for entity) as you have mentioned in your reply. My script works only when entity number is in row one but doean't work when entity is in row 2 as given above.
Any pointers will be appreciated.
Thanks in Advance!!
If you look a the example in the FDM admin guide (Section = Storing temporary values), you will see that you will need to perform a test in the script attached to the amount field, to see if the line begins with the word "CostCentre" (uses StrRecord rather than strfield). the example is an exact example of what you are trying to achieve, and it does not matter what line(s) in the data file that it occurs.