1. CLICK HERE TO JOIN WHATSAPP CHANNEL FOR TDL PROGRAMS

Little Code to know the existing price lists date for a given price list

Discussion in 'Free Source Codes' started by Devendra_Rawat, Feb 2, 2020.

    
Tags:
  1. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    Below modification helps user to know existing price list dates for selected price list before creating new one.



    Code:
    ;This Part added on 02/02/2020
;----------------------------------------------------------------------------------------------------------------------------------------------
[#Field: MPL Price Date]
    Add    : Option    : Wh MPL Price Date :  Yes     ;You can add your condition here

[!Field    : Wh MPL Price Date]
    Use    : MPL Price Date
    Add    : Table    : WhPriceListDate, NewDate
    Add    : Show Table    : Always
    Add    : Dynamic    : $$SysName:NewDate
    Add    : Trigger    : New Date : "NewDate"=$$EditData
    Add    : Key        : Create New Date
  
  
[Collection: New Date]
    Title       : $$LocaleString:"New Date"
    List Name   : "New Date"
    Format      : $Name, 10
    Format        : "Create", 10
  
    ClientOnly    : Yes
  

[Key: Create New Date]

    Key     : Alt+C
    Action  : Create : New Date
  
[Report: New Date]    ;; Auto Report

    Auto        : Yes
    Title        : $$LocaleString:"New Date"

[Form: New Date]

    No Confirm  : Yes
    Full Width  : No

    Space Top   : 1
    Space Bottom: 1
    Space Left  : 1
    Space Right : 1

    Background  : @@SV_UNWHITE
    Parts       : New Date
    Output      : New Date

[Part: New Date]

    Lines       : Form SubTitle, New Date
    Local       : Field : Form SubTitle : Info : $$LocaleString:"New Date"

    [Line: New Date]

        Fields      : New Date

        [Field: New Date]

            Use         : Short Date Field
            Set as        : ##svCurrentDate
            Width        : 10
            Validate    : NOT $$IsEmpty:$$Value
          
  
[Collection    : WhPriceListDateSRC]
  
    Type        : FullPriceList     : StockItem
    Child Of    : ##SStockItem
    Fetch        : Date, PriceLevel

[Collection    : WhPriceListDate]
  
    Collection    : WhPriceListDateSRC : StockItem
    Title        : "Price Lists"
    By            : PriceLevel : $PriceLevel
    Sort        : @@Default    : $Date, $PriceLevel
  
    Format        : $Date, 10: Universal Date
    Format        : "Exists", 6

    Sub Title    : "Date", "Status"
    Filter        : SelectedPriceList  
  
  
[System        :Formulae]
SelectedPriceList        : $PriceLevel = ##SVPriceLevel

;-------------------------------------------------------------------------------------------------------------------------------  
;Above Part added on 02/02/2020
     
    Devendra_Rawat, Feb 2, 2020
    #1
    Suyash Narnolia, Siddharth Agrawal, 9779845157470 and 6 others like this.


  2. Siddharth Agrawal

    Siddharth Agrawal New Member


    Seems like you know a fair bit on Price Levels and Price Lists.
    I have written a code to import Pricelist applicable from a New date from excel, but have not been able to succeed in it.
    It would be really a very big help if you could help out a bit.
     
    Siddharth Agrawal, May 31, 2021
    #2
    Devendra_Rawat likes this.


  3. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    :eek::eek:

    yes, if you are ready to share required " part of the code", tally company.
     
    Devendra_Rawat, May 31, 2021
    #3


  4. Siddharth Agrawal

    Siddharth Agrawal New Member


    I have a basic requirement to add a new pricelist (New Date) imported from EXCEL .

    I have attached the sample EXCEL and Company files for your reference


    Code:
    
[#Menu: Gateway of Tally]
    Add    : Key Item    : Price List Import    : C : EXECUTE    : Price List Import

[Report: Price List Import]
    Form    : Price List Import
    Title    : $$LocaleString:"Price List Import"
    Auto    : Yes

    [Form: Price List Import]
        No Confirm      : Yes
        Space Top       : 1
        Space Left      : 1
        Space Right     : 1
        Space Bottom    : 1
        Width              : 50% Page
        Height             : 50% Page
        Vertical Align  : Centre
        Parts            : Price List Import Title, Price List Import Details
        On              : Form Accept   : Yes   : CALL  : Price List Import Func
        Button            : GST Stockitem Template Button
    
        [Part: Price List Import Title]
            Lines: Price List Import , Price List Import Info
            [Line: Price List Import]
                Fields          : Form Sub Title
                Space Bottom    : 0.25    
                Local           : Field : Form Sub Title : Info    : $$LocaleString:"Price List Import Utility"
            [Line: Price List Import Info]
                Field           : Simple Field
                Space Bottom    : 0.75    
                Local           : Field : Simple Field : Info       : $$LocaleString:"(Enter Your Excel Sheet Path)"
                Local           : Field : Simple Field : Full Width : Yes
                Local           : Field : Simple Field : Align      : Centre

        [Part: Price List Import Details]
            Lines: Price List Import Date, Price List Import Level, Price List Import StockGroup, Price List Import FileName, Price List Import SheetName
            [Line: Price List Import Date]
                Field        : Medium Prompt, Price List Import Date
                Local        : Field: Medium Prompt    : Info : "Applicable From (Date)"
                Space Bottom: 1
                [Field: Price List Import Date]
                    Type    : Date
                    Set as    : ##SVCurrentDate
                    Modifies: PLDate
            [Line: Price List Import Level]
                Field        : Medium Prompt, Price List Import Level
                Local        : Field: Medium Prompt    : Info : "Price Level"
                Space Bottom: 1
                [Field: Price List Import Level]
                    Use        : Name Field
                    Table     : Price Levels
                    Show Table: On Blank
                    Modifies : PLLevel
            [Line: Price List Import StockGroup]
                Field        : Medium Prompt, Price List Import StockGroup
                Local        : Field: Medium Prompt    : Info : "Stock Group"
                Space Bottom: 1
                [Field: Price List Import StockGroup]
                    Use        : Name Field
                    Table     : StkGrpCollection
                    Show Table    : On Blank
            [Line: Price List Import FileName]
                Field        : Medium Prompt, Price List Import FileName
                Local        : Field: Medium Prompt    : Info : $$LocaleString:"Excel File Path :"
                Space Bottom: 1
                [Field        : Price List Import FileName]
                    Use        : Name Field
                    Set as    : "D:\My TALLY Data\Price List Import from Excel TDL\Price List Import.xlsx"
                    Width    : @@NarrWidth
                    Full Width: Yes
            [Line: Price List Import SheetName]
                Field       : Medium Prompt, Price List Import SheetName
                Local       : Field : Medium Prompt    : Info : $$LocaleString:"Excel Sheet Name:"
                [Field        : Price List Import SheetName]
                    Use        : Name Field
                    Set as    : "PriceLevels"
                    Width    : @@NarrWidth
                    Full Width: Yes
                
[Variable: PLDate]
    Type : Date

[Variable: PLLevel]
    Type : String

[System: Variable]
    PLDate    : ##SVCurrentDate
    PLLevel    : ""

[Function    : Price List Import Func]
    Variable    : RCnt            : Number    : 2
    Variable    : APFilePath    : String
    Variable    : SPShetName    : String

     000    : Set                : APFilePath    : #PriceListImportFileName
    010    : Set                : SPShetName    : #PriceListImportSheetName
    020    : Open File            : ##APFilePath    : Excel    : Read
    030    : Set Active Sheet    : ##SPShetName

    040    : While    : Not $$IsEmpty:($$FileReadCell:##RCnt:1)
    050    :    Increment    : RCnt
    060    : End While

    070    : Start Progress    : ##RCnt    : ##SVCurrentCompany    : "Price List import in progress" : "Please Wait..."
    080    : Set                : RCnt    : 2

    090    : While    : Not $$IsEmpty:($$FileReadCell:##RCnt:1)

    100    :     NEW Object        : Stock Item        : ($$FileReadCell:##RCnt:1)    : Yes

    111 :         Log: $$FileReadCell:##RCnt:1
    112 :         Log: $$FileReadCell:##RCnt:2
    120 :         INSERT COLLECTION OBJECT        : FullPriceList
    130 :            SET VALUE    : Date            : ##PLDate
    140 :            SET VALUE    : PriceLevel    : ##PLLevel
    150 :            INSERT COLLECTION OBJECT        : PRICELEVELLIST
;    220 :                SET OBJECT
    160 :                SET VALUE    : STARTINGFROM    : ""
    170 :                SET VALUE    : ENDINGAT        : ""
    180 :                SET VALUE    : RATE            : $$AsAmount:$$FileReadCell:##RCnt:2
    190 :                SET VALUE    : DISCOUNT        : ""
    220 :            SET OBJECT

    200 :            Set Target..
    210 :        Set Target..

;    691 : Call: UpdateStockItemPriceList:$Name

;    230 :    SAVE Target
    240 :    Accept Alter

    670    :     Set                : RCnt : ##RCnt+1
    680    :     Show Progress    : ##RCnt
    690    :     End While




    700 : Close File
    710    : Msg Box: "Message": "Price List Imported Successfully !!!"
    720 : Return


[Collection: StkGrpCollection]
    Title        : "List of Stock Groups"
    Type         : StockGroup
    Variable     : StockGroupName
    Report         : InventoryMasterReport
    Trigger     : TriggerStkGroupList
    Align         : Centre
    Belongs To    : Yes
     

    Attached Files:

    Siddharth Agrawal, May 31, 2021
    #4


  5. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    Ok, shortly
     
    Devendra_Rawat, Jun 1, 2021
    #5


  6. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    Your code will work only if

    1. Stock Items are already there.
    2. Price is list for the date is created first time, if data exists for stock item for that date and it will just append to it.

    I have modified the functions

    Code:
    [Function    : Price List Import Func]
    Variable    : RCnt          : Number    : 2
    Variable    : APFilePath    : String
    Variable    : SPShetName    : String
    Variable     : StockItem         : String   
    Variable    : pldate        : Date
    Variable    : plName        : String


    000    : Set                : APFilePath    : #PriceListImportFileName
    010    : Set                : SPShetName    : #PriceListImportSheetName
    011        : Set                : Pldate        : #PriceListImportDate
    012        : Set                : plname        : #PriceListImportLevel
    020    : Open File            : ##APFilePath    : Excel    : Read
    030    : Set Active Sheet    : ##SPShetName

    040    : While    : Not $$IsEmpty:($$FileReadCell:##RCnt:1)
    050    :    Increment    : RCnt
   
    060    : End While

    070    : Start Progress    : ##RCnt    : ##SVCurrentCompany    : "Price List import in progress" : "Please Wait..."
    080    : Set                : RCnt    : 2

    090    : While    : Not $$IsEmpty:($$FileReadCell:##RCnt:1)
    095       : Set        :    StockItem :($$FileReadCell:##RCnt:1)
    100        : Call    :    Update Price List Import Func
    150    :     Set                : RCnt : ##RCnt+1
    200    :     Show Progress    : ##RCnt

    300    : End While

    400        : Close File
    500    : Msg Box: "Message": "Price List Imported Successfully !!!"
    600     : Return
   


[Function    : Update Price List Import Func]
    Object    : Stock Item    : ##StockItem
   
    Variable    : vStrQty    :  Quantity
    Variable    : vEndQty    :  Quantity
    Variable    : vDiscount    :  Amount
    Variable    : vstrRate    :  String

   
    005        :    Log: "You are Here"
   
    015        :    SET TARGET
    016        :    Log:##StockItem
  ; 100    :     NEW Object        : Stock Item        : ($$FileReadCell:##RCnt:1)    : Yes

    111 :         Log: $$FileReadCell:##RCnt:1
    112 :         Set: vstrRate    : ($$FileReadCell:##RCnt:2)
    112a    :      Log: ##vstrRate
   
    115    :          Log:##PLName

    120 :         INSERT COLLECTION OBJECT        : FullPriceList
;    115    :            Set Object
    130 :            SET VALUE    : Date            : $$Date:##PLDate
    140 :            SET VALUE    : PriceLevel        : $$String:##PLName
    150 :            INSERT COLLECTION OBJECT        : PRICELEVELLIST
;   155 :                SET OBJECT
    160 :                Do If    : (NOT $$IsEmpty:##vStrQty)    :    SET VALUE    : STARTINGFROM    : $$AsQty:0
    170 :                Do If    : (NOT $$IsEmpty:##vEndQty)    :    SET VALUE    : ENDINGAT        : $$AsQty:0
    180 :                SET VALUE    : RATE    : $$AsRate:##vstrRate
    190 :                Do If    : (NOT $$IsEmpty:##vDiscount)    :    SET VALUE    : DISCOUNT        : $$AsAmount:0
    ;220 :            SET OBJECT

    200 :            Set Target    : ..
    210 :        Set Target    : ..

;    691 : Call: UpdateStockItemPriceList:$Name

;    230 :    SAVE Target
    240 :    Accept Alter
     
    Devendra_Rawat, Jun 1, 2021
    #6
    Siddharth Agrawal likes this.


  7. Siddharth Agrawal

    Siddharth Agrawal New Member


    Thanks a TON for the help.
    Had been stuck for the past 15 days.
    This little change is working wonders.
    This is all I wanted my little code to do. Nothing more complex.

    Thanks again..

    I hope other people in the forum can also benefit from this..
     
    Siddharth Agrawal, Jun 2, 2021
    #7


  8. Siddharth Agrawal

    Siddharth Agrawal New Member


    Is there also a way to delete unwanted or OLD pricelists ?

    I used your 1st code. It shows all the prices lists created over the years for the same price level. Now I have a huge list of around 35-40 lists (date wise).
    I want to delete the really old ones.

    Is that possible ?

    If yes, Kindly guide me ??
     
    Siddharth Agrawal, Jun 2, 2021
    #8


  9. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    Use a FILTER in your Price List collection.

    Alter the PriceList name you do not want....ad OLD at the end.
    Then filter the collection to exclude List with OLD in its name.
     
    Amit Kamdar, Jun 2, 2021
    #9
    panam and Devendra_Rawat like this.


  10. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    Best way is to .. go to price list, and press control D on each line until all stock items are deleted from the price list..

    If you want some short cut then @Amit Kamdar ji has given a very good alternative..

    by the way... when Tally has already provided a complete TDL on this, which is there in the forum also , why do you want a new TDL on this?
     
    Devendra_Rawat, Jun 3, 2021
    #10
    panam and Siddharth Agrawal like this.


  11. Siddharth Agrawal

    Siddharth Agrawal New Member


    Thank a lot again !!

    I was unaware of the Ctrl+D function !!
     
    Siddharth Agrawal, Jun 5, 2021
    #11
    panam likes this.


  12. ANANDH-laxmi

    ANANDH-laxmi New Member


    THE SECOND FUNCION IS NOT WORKING STOCK GROUP TABLE IS NOT SHOWING



    [Line: Price List Import Level]
    Field : Medium Prompt, Price List Import Level
    Local : Field: Medium Prompt : Info : "Price Level"
    Space Bottom: 1
    [Field: Price List Import Level]
    Use : Name Field
    Table : Price Levels
    Show Table: On Blank
    Modifies : PLLevel
    [Line: Price List Import StockGroup]
    Field : Medium Prompt, Price List Import StockGroup
    Local : Field: Medium Prompt : Info : "Stock Group"
    Space Bottom: 1
    [Field: Price List Import StockGroup]
    Use : Name Field
    Table : StkGrpCollection
    Show Table : On Blank
    [Line: Price List Import FileName]
     
    ANANDH-laxmi, Jan 19, 2024
    #12


Share This Page