2 Replies Latest reply on Feb 22, 2016 5:33 PM by bigtom

    Different prices based on adress and date of service

    MAKALENG

      Hi all

       

      i have close to 9 Provinces (states)  Tables (actually 18 cos all 9 provinces have 2015 and 2016 price lists)

      each Table has code, description and amount as field names,

      code and description is the same on all tables , where they differ is the amount [based on the province(state) and date (Year)of service]

      the problem is

      i need calculation for Amount field on Invoice to be calculated  by checking on Contact Details Table which state is the customer from and then date of service which year was service renderd and bill them accordingly

       

      TABLE 1: NORTH PROVINCE 2015

      CODE : 0001 DESCRIPTION: COUNSELLING AMOUNT: 100

       

      TABLE 2 IS NORTH PROVINCE 2016

      CODE : 0001 DESCRIPTION: TREATMENT AMOUNT: 150

       

      TABLE 3 IS SOUTH PROVINCE 2015

      CODE : 0001 DESCRIPTION: COUNSELLING AMOUNT: 120

       

      TABLE 4 IS SOUTH PROVINCE 2016

      CODE : 0001 DESCRIPTION: TREATMENT AMOUNT: 170

       

      ALL TABLES ARE ACCORDINGLY RELATED, I STRUGGLE WITH AMOUNT CALCULATION

        • 1. Re: Different prices based on adress and date of service
          siplus

          if you struggle, ther're not "accordingly " related I also see a code 0001 description constant useless as such, in your problem description. All you need is a relationship from Payments to ContactDetails, with Year(billdate), descriptionID and provinceID which will give you the amont to be applied.Accordingly.

          • 2. Re: Different prices based on adress and date of service
            bigtom

            Where is the date of service information located? Sounds like Contact Details.

             

            If this is just a pricing catalog, why is it not all in one table? Discerning the correct table to go look into with your arrangement might not be the easiest to do.  Even Siplus seems to be assuming a single table is the case. I would advise it.

             

            As I understand it, you need to be on the Invoices table layout and then use data from ContactDetails to look up a value in the correct PriceCatalog table to then put that value into a field in the Invoices layout. Is this correct?