TDL to connect to sql server

Discussion in 'Tally Integration' started by Sushma, Apr 15, 2019.

         
  1. Sushma

    Sushma New Member


    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
     

    Attached Files:



  2. Amit Kamdar

    Amit Kamdar Moderator Staff Member


    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.
     


  3. Sushma

    Sushma New Member


    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
     

    Attached Files:



  4. Sushma

    Sushma New Member


    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
     


  5. Amit Kamdar

    Amit Kamdar Moderator Staff Member


    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'"
     


  6. Sushma

    Sushma New Member


    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
     


  7. Amit Kamdar

    Amit Kamdar Moderator Staff Member


    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.
     


  8. Sushma

    Sushma New Member


    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 SVAttdVoucherDate:DateTime
    ;;Return :Logical
    ;;Returns : Date
    ;;Variable: TSPL FFE SVAttdVoucherDate : Date

    ;;Parameter: ##TSPLFFESVAttdVoucherDate:Date

    ;;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 : $$IsSysNameEqual:production:$AttendanceProductionType:AttendanceType:$AttendanceType
    150 : SET VALUE : AttdTypeValue : $$TGTObject:$$AsQty:mad:ProdAttdValue
    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
     


  9. Sushma

    Sushma New Member


    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
     


  10. Amit Kamdar

    Amit Kamdar Moderator Staff Member


    Mater ODBC Excel Sheet : ##TSPLFFESVAttdVoucherDate
     


  11. Sushma

    Sushma New Member


    Sir,

    It is not working. showing same message No Entries in Voucher.


    Thanks and Regards,
    Sushma A Jainar
     


  12. Sushma

    Sushma New Member


    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
     


  13. Sushma

    Sushma New Member


    Sir,

    PFA of Tally screen. how to insert single entry in Day Book.

    Please help me sir


    Thanks and Regards,
    Sushma A Jainar
     

    Attached Files:



  14. Amit Kamdar

    Amit Kamdar Moderator Staff Member


    Do not run your function 3 times................
     


  15. Sushma

    Sushma New Member


    Sir,

    How to convert Minutes to Hrs using tdl.



    Thanks and Regards,
    Sushma A Jainar
     


  16. Amit Kamdar

    Amit Kamdar Moderator Staff Member


    search for "time difference calculator" in this website.
     


  17. Sushma

    Sushma New Member


    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 :mad:@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 : $$IsSysNameEqual:production:$AttendanceProductionType:AttendanceType:$Status
    150 : SET VALUE : AttdTypeValue : $$TGTObject:$$AsQty:mad:ProdAttdValue
    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
     


  18. Amit Kamdar

    Amit Kamdar Moderator Staff Member


    Try this way....

    150 : SET VALUE : AttdTypeValue : $$TGTObject:$$AsQty:(@@ProdAttdValue/60)
     


  19. Sushma

    Sushma New Member


    Sir,

    This also not working.


    Thanks and Regards,
    Sushma A Jainar
     


  20. Amit Kamdar

    Amit Kamdar Moderator Staff Member


    temp ..... remove $BaseUnits from local formula.............. and put your original $Duration/60
     


  21. Sushma

    Sushma New Member


    Sir,

    I changed the code. now it is working fine thank you for your support.


    140 : IF : $$IsSysNameEqual:production:$AttendanceProductionType:AttendanceType:$Status
    150 : SET VALUE : AttdTypeValue : $$TGTObject:$$AsQty:mad:ProdAttdValue / 60
    160 : ELSE:
    170 : SET VALUE : AttdTypeTimeValue : $Duration / 60
    180 : END IF


    Thanks and Regards,
    Sushma A Jainar
     


  22. Sushma

    Sushma New Member


    Ok Sir I will try this also.



    Thanks and Regards,
    Sushma A Jainar
     


  23. Sushma

    Sushma New Member


    Sir,

    How to mention week off, holiday and leaves on hours basis in tally.



    Thanks and Regards,
    Sushma A Jainar
     


  24. Vijay Shetye

    Vijay Shetye New Member


    Kindly contact me on 9820309919 will help you with the solution
     


  25. Sushma

    Sushma New Member


    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
     


Share This Page