I written tdl code read data from sql server and update in tally. I want to pass the voucher date in my sql query I have created user interface to accept voucher date the voucher date and attendance date should be same. Please help me to solve the problem Thanks
Tried a bit with different methods but cud not achieve results. It shows, no data. Also FYI..... the attendance value and Attendance Units i.e. 10 Hrs.... both are separate. You can try putting a column in excel format as well as the Function in TDL.
Sir, Thank you for reply. If I write the following queries it is working fine. SQL : "Select * From TallyAttendance" SQL : "Select * From TallyAttendance where AttendanceDate='1-10-2019'" but I don't want to hard code the date value. Here I am attaching the tally front end screen which I created for accept date. that date I want to pass in the SQL query. or any other options are their for read values from database table or excel using the user entered date in collection or function. Please help me. Thanks and Regards, Sushma A Jainar
Sir, Can we use if condition or do while condition in collection or function to compare attendance date with user entered date. if both match then the record should update in tally database. Thanks and Regards, Sushma A Jainar
Try this.... since you have already a variable defined...... it will pick up value from there...... SQL : "Select * From TallyAttendance where AttendanceDate='##TSPLFFESVAttdVoucherDate'" OR SQL : "Select * From TallyAttendance where AttendanceDate='$$String:##TSPLFFESVAttdVoucherDate'"
Sir, above solution is not working it showing error msg : FUNCTION: 'TSPL FFE ImportAttendanceVoucher' LABEL:220: No Entries in Voucher Thanks and Regards, Sushma A Jainar
error message was there before also....check your previous screenshot........... in post # 3 the message means it is not importing.....any data.......... nothing to do with the variable name.
Sir, SQL : "Select * From TallyAttendance" the above sql query I will not change. but can I add any If condition or While loop in the following function. [Function: TSPL FFE ImportAttendanceVoucher] LOCAL FORMULA : ProdAttdValue : $$String:$AttendanceValue + $BaseUnits:AttendanceType:$AttendanceType ;;Parameter: TSPL FFE SVAttdVoucherDateateTime ;;Return :Logical ;;Returns : Date ;;Variable: TSPL FFE SVAttdVoucherDate : Date ;;Parameter: ##TSPLFFESVAttdVoucherDateate ;;Return :Logical ;; To Fetch Object Production Type 'Absent' in case of Remote Company 00 : IF : $$IsRemoteCompany 01 : CALL : TSPL FFE FO AttendType 02 : ENDIF ;; Create Production Type "Absent" if not yet created 05 : IF : $$IsEmpty:$Name:AttendanceType:"Absent" 10 : NEW OBJECT : Attendance Type 20 : SET VALUE : Name : "Absent" 30 : SET VALUE : Attendance Production Type : "Leave Without Pay" 40 : SET VALUE : Attendance Period : "Days" 50 : SAVE TARGET 60 : ENDIF 70 : NEW OBJECT : VOUCHER 80 : SET VALUE : Date : ##TSPLFFESVAttdVoucherDate ;;80 : SET VALUE : Date : $$Date($$StringPart:$AttendanceDate:8:2 +"/"+ $$StringPart:$AttendanceDate:5:2 + "/"+$$StringPart:$AttendanceDate:0:4)) ;;"1-Apr-2012" ;;$Date 90 : SET VALUE : VoucherTypeName : $$VchTypeAttendance 100 : MSGBOX : "Status" : ##TSPLFFESVAttdVoucherDate 110 : WALK COLLECTION : TSPL FFE Vch Collection ;;111 : IF : @@IsMyLedger > 0 ;;111 : IF @@RbcInvDate = @@RbcInvDateVch) ;;111 : IF : ($AttendanceDate = ##TSPLFFESVAttdVoucherDate) 111 : INSERT COLLECTION OBJECT : AttendanceEntries ;;112: Do If: ($$AttendanceDate = ##TSPLFFESVAttdVoucherDate) :Return:True 112 : IF : $$IsRemoteCompany 113 : CALL : TSPL FFE FO AttendProdType 114 : ENDIF 120 : SET VALUE : Name : $EmployeeName 130 : SET VALUE : AttendanceType : $AttendanceType 140 : IF : $$IsSysNameEqualroduction:$AttendanceProductionType:AttendanceType:$AttendanceType 150 : SET VALUE : AttdTypeValue : $$TGTObject:$$AsQtyProdAttdValue 160 : ELSE: 170 : SET VALUE : AttdTypeTimeValue : $AttendanceValue 180 : END IF 190 : SET TARGET : .. ;;200 : END IF 200 : END WALK ;;210: Return:False 210 : SET TARGET : .. 220 : SAVE TARGET ;; Report displaying imported Attendance Voucher 230 : MSGBOX : "Status" : "Process completed successfully!!" 240 : RETURN Thanks and Regards, Sushma A Jainar
Sir, Please find the following code which I am able to pass parameter in sql query. Now it is working. [Collection: TSPL FFE AttendanceDataExcelColl] ODBC : "Driver=SQL Server;Server=SERVER-PC\SQLEXPRESS;Database=etimetracklite1;Trusted_Connection=True;" SQL : "Select * From TallyAttendance where AttendanceDate = "+"'"+@@MaterODBCExcelSheet+"'" SQLObject : TSPL FFE Attendance Excel Voucher Client Only : Yes [System : Formula] Mater ODBC Excel Sheet : "9-Jan-2019" but in the above I have hard coded the date value. how to assign the date to the any variable accepted from the user. Thanks and Regards, Sushma A Jainar
Sir, It is not working. showing same message No Entries in Voucher. Thanks and Regards, Sushma A Jainar
Sir, [System : Formula] Mater ODBC Excel Sheet : $$String:##TSPLFFESVAttdVoucherDate I used the above code. It is working fine thank you for your support. Thanks and Regards, Sushma A Jainar
Sir, PFA of Tally screen. how to insert single entry in Day Book. Please help me sir Thanks and Regards, Sushma A Jainar
Sir, I am using the following code. [System : Formula] Mater ODBC Excel Sheet : $$String:##TSPLFFESVAttdVoucherDate Duration Hours : $Duration/60 [Function: TSPL FFE ImportAttendanceVoucher] LOCAL FORMULA : ProdAttdValue @DurationHours + $BaseUnits:AttendanceType:$Status 70 : NEW OBJECT : VOUCHER 80 : SET VALUE : Date : ##TSPLFFESVAttdVoucherDate 90 : SET VALUE : VoucherTypeName : $$VchTypeAttendance 100 : MSGBOX : "Status" : ##TSPLFFESVAttdVoucherDate 110 : WALK COLLECTION : TSPL FFE ExcelInventory 111 : INSERT COLLECTION OBJECT : AttendanceEntries 120 : SET VALUE : Name : $EmployeeId 130 : SET VALUE : AttendanceType : $Status 140 : IF : $$IsSysNameEqualroduction:$AttendanceProductionType:AttendanceType:$Status 150 : SET VALUE : AttdTypeValue : $$TGTObject:$$AsQtyProdAttdValue 160 : ELSE: 170 : SET VALUE : AttdTypeTimeValue : @@DurationHours 180 : END IF 190 : SET TARGET : .. 200 : END WALK 210 : SET TARGET : .. 220 : SAVE TARGET ;; Report displaying imported Attendance Voucher 230 : MSGBOX : "Status" : "Process completed successfully!!" 240 : RETURN it is not working sir. showing 150: Action execution failed. If I use only this it is working. [System : Formula] Mater ODBC Excel Sheet : $$String:##TSPLFFESVAttdVoucherDate Duration Hours : $Duration Thanks and Regards, Sushma A Jainar
Sir, I changed the code. now it is working fine thank you for your support. 140 : IF : $$IsSysNameEqualroduction:$AttendanceProductionType:AttendanceType:$Status 150 : SET VALUE : AttdTypeValue : $$TGTObject:$$AsQtyProdAttdValue / 60 160 : ELSE: 170 : SET VALUE : AttdTypeTimeValue : $Duration / 60 180 : END IF Thanks and Regards, Sushma A Jainar
Sir, How to mention week off, holiday and leaves on hours basis in tally. Thanks and Regards, Sushma A Jainar
Ok sir thank you for reply. Now I got the solution. I am working on payroll system integration in Tally ERP 9. I will contact you for further developments. Thanks and Regards, Sushma A Jainar
