Import From Excel for Alter/Edit/Modification In Purchase ENtry

I Try bellow code not work, Please any one can guide me ?

I have Create 3 Field in purchase Voucher and i want to Import Data From Excel to fill same field as per reference number(Party Invoice No.)




[#Form: Purchase Color]
Add : Part : At End : Parcel Details

[Partarcel Details]
Linearcel Details

[Linearcel Details]
Field:Simple Prompt,Parcel Logistic,Short Prompt,Parcel PickupBy,Medium Prompt,Parcel DeleverdBy
Local: Field: Simple Prompt : Info: "Logistic Name"
Local: Field: Short Prompt : Info: "Pickup By"
Local: Field: Medium Prompt : Info: "DeleverdBy"

[Fieldarcel Logistic]
Use: Name Field
Storage:LogisticName
Width :15
[Fieldarcel PickupBy]
Use: Name Field
Storage:LPPickupBy
Width :10
[Fieldarcel DeleverdBy]
Use: Name Field
Storage:LPDeleverdBy
Width :10

[System:UDF]

LPName : String: 20051
LPPickupBy : String: 20052
LPDeleverdBy : String: 20053

;; Functions Con

[#Form : DayBook]
Add : Button : ImportLogDetails

[#Form: Ledger Vouchers]
Add : Button : ImportLogDetails

[Button: ImportLogDetails ]
Key: Ctrl+6
Title: Imp Log
Action: Call : ImportLogFun



[System:Formulae]
IsSameRef : $ReferenceNumber = $PartyInvNum
MstrIds : "ID:" + $$String:$MasterId

[Function:ImportLogFun]
Variable : MSTID : String

;00:START PROGRESS$$NumItems:LogExcelCollection) :"Importing Vouchers"@CmpMailName:"Importing Jsons"


10 : WALK COLLECTION : ImportExcelCells
;20 : If : $ReferenceNumber = "53/22-23";$_1

;; Set Object In Edit Mode
30 : NEW OBJECT : VOUCHER : @@MstrIds : Yes
31 : If : $ReferenceNumber = $_1
40 : Set Value: LPName : $_3
50 : Set Value: LPPickupBy : $_4
60 : Set Value: LPDeleverdBy: $_5

60a : Log:$LPName
60b : Log:$LPPickupBy
60c : Log:$LPDeleverdBy

90 : Else
91 : Return
92 : End If
100 : ACCEPT ALTER
101 : Save Target

;150 : Else
;160 : Return
;200 : End If
200 : END WALK
280 : Msg Box : "Status" : "Suscsessfully Impoted"

[Variable: ImpExlFileCell]

Type : String
Persistent : Yes

[Variable: ImpExlSheetCell]

Type : String
Persistent : Yes
[System: Variable]
ImpExlFileCell : "D:\TDL 2023\Logistc Info Import\Logistic Data.xls"
ImpExlSheetCell : "Jan23"

[Collection:ImportExcel Cells]
ODBC : "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + ##ImpExlFileCell +""
SQL : "SELECT * FROM ["+##ImpExlSheetCell+"$]"

;select $_1, $_2, $_3, $_4, $_5 from ImportExcelCells

 

probably you need to alter the collection. When I tried to read the value of the UDF and make it visible in the ledger report... First it didnt worked... adding this alteration of Voucher of the company collection, it worked for me


[#Collection : Vouchers of compnay]
Fetch:<<comma separated UDFs>>

 

Thank You to reply sir,

But i didn't understand, which collection i want to alter, just one collection i have used which is import from Excel, and 2nd collection where i used

 

try adding this following collection alteration... I guess first you need to let the voucher object to look values for the UDFs...

[#Collection : Vouchers of company]
Fetch:LPName, LPPickupBy, LPDeleverdBy

 

Sir,

I have changes as per your suggestions , code is ruining Properly but value not Fetching in UDF feilds


[#Collection : Vouchers of company]
Fetch:LPName, LPPickupBy, LPDeleverdBy[/QUOTE]
[Collection: VoucherCollPur]
Type : Voucher
Fetch:LPName,LPPickupBy,LPDeleverdBy
;; Functions Con

[#Form : DayBook]
Add : Button : ImportLogDetails

[#Form: Ledger Vouchers]
Add : Button : ImportLogDetails

[Button: ImportLogDetails ]
Key: Ctrl+6
Title: Imp Log
Action: Call : ImportLogFun



[System:Formulae]
IsSameRef : $ReferenceNumber = $PartyInvNum
MstrIds : "ID:" + $$String:$MasterId

[Function:ImportLogFun]
Variable : MSTID : String
Variable: TotalItemsImport: Number

00: Set :TotalItemsImport: $$NumItems:ImportExcelCells
05:START PROGRESS:##TotalItemsImport :"Importing Vouchers"@CmpMailName:"Importing Excel"


10 : WALK COLLECTION : ImportExcelCells
;20 : If : $ReferenceNumber = "53/22-23";$_1

;; Set Object In Edit Mode
30 : NEW OBJECT : VOUCHER : @@MstrIds : Yes
31 : If : $ReferenceNumber = $_1
32: Insert Collection Object:VoucherCollPur
40 : Set Value: LPName : $_3
50 : Set Value: LPPickupBy : $_4
60 : Set Value: LPDeleverdBy: $_5

60a : Log:$LPName
60b : Log:$LPPickupBy
60c : Log:$LPDeleverdBy
80 : Save Target:..

92 : End If
100 : ACCEPT ALTER
101 : Save Target

;150 : Else
;160 : Return
;200 : End If
200 : END WALK
201 :End Progress
280 : Msg Box : "Status" : "Suscsessfully Impoted"

[Variable: ImpExlFileCell]

Type : String
Persistent : Yes

[Variable: ImpExlSheetCell]

Type : String
Persistent : Yes
[System: Variable]
ImpExlFileCell : "D:\TDL 2023\Logistc Info Import\Logistic Data.xls"
ImpExlSheetCell : "Jan23"

[Collection:ImportExcelCells]
ODBC : "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + ##ImpExlFileCell +""
SQL : "SELECT * FROM ["+##ImpExlSheetCell+"$]"

;select $_1, $_2, $_3, $_4, $_5 from ImportExcelCells

 

[Collection: VoucherCollPur]
Type : Voucher
Fetch:LPName,LPPickupBy,LPDeleverdBy
;; Functions Con

[#Form : DayBook]
Add : Button : ImportLogDetails

[#Form: Ledger Vouchers]
Add : Button : ImportLogDetails

[Button: ImportLogDetails ]
Key: Ctrl+6
Title: Imp Log
Action: Call : ImportLogFun



[System:Formulae]
IsSameRef : $ReferenceNumber = $PartyInvNum
MstrIds : "ID:" + $$String:$MasterId

[Function:ImportLogFun]
Variable : MSTID : String
Variable: TotalItemsImport: Number

00: Set :TotalItemsImport: $$NumItems:ImportExcelCells
05:START PROGRESS:##TotalItemsImport :"Importing Vouchers"@CmpMailName:"Importing Excel"


10 : WALK COLLECTION : ImportExcelCells
;20 : If : $ReferenceNumber = "53/22-23";$_1

;; Set Object In Edit Mode
30 : NEW OBJECT : VOUCHER : @@MstrIds : Yes
31 : If : $ReferenceNumber = $_1
32: Insert Collection Object:VoucherCollPur
40 : Set Value: LPName : $_3
50 : Set Value: LPPickupBy : $_4
60 : Set Value: LPDeleverdBy: $_5

60a : Log:$LPName
60b : Log:$LPPickupBy
60c : Log:$LPDeleverdBy
80 : Save Target:..

92 : End If
100 : ACCEPT ALTER
101 : Save Target

;150 : Else
;160 : Return
;200 : End If
200 : END WALK
201 :End Progress
280 : Msg Box : "Status" : "Suscsessfully Impoted"

[Variable: ImpExlFileCell]

Type : String
Persistent : Yes

[Variable: ImpExlSheetCell]

Type : String
Persistent : Yes
[System: Variable]
ImpExlFileCell : "D:\TDL 2023\Logistc Info Import\Logistic Data.xls"
ImpExlSheetCell : "Jan23"

[Collection:ImportExcelCells]
ODBC : "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + ##ImpExlFileCell +""
SQL : "SELECT * FROM ["+##ImpExlSheetCell+"$]"

;select $_1, $_2, $_3, $_4, $_5 from ImportExcelCells[/QUOTE]


SIR I DIDN'T RESOLVED THIS PROBLEM, I AM NOT EXPERT TO UNDERSTAND YOUR EXPERT SUGGESTION PLEASE GUILD ME LIKE FRESHER PLEASE

 

SIR I DIDN'T RESOLVED THIS PROBLEM, I AM NOT EXPERT TO UNDERSTAND YOUR EXPERT SUGGESTION PLEASE GUILD ME LIKE FRESHER PLEASE

 

first thing: I didnt recall anything I have said requires an expert knowledge... what you need to do is., just copy the following code and paste it to your code.... My apologies, if it was mis conveyed....

Code:

[#Collection : Vouchers of company]
Fetch:LPName, LPPickupBy, LPDeleverdBy

check after, whether it is working... if not let me know., we may find an alternative way...

 

Sir,

I try copy above coll in my code but still its not work