3 Replies Latest reply on Oct 27, 2008 5:14 AM by bcooney

    Creating Discounts

    seanhunt

      Title

      Creating Discounts

      Post

      I am a photographer and am creating a relational database to deal with all aspects of image sales. Sometimes a client will request a 50% discount on a second use of an image and sometimes a further 50% discount on any more uses. I want to create a script that will automatically reduce the image price of the lowest priced second image by 50% (the same image can have different prices depending say on the size of a page it will be used eg 1/4 page, 1/2 page etc.) then all further image uses at 50%. Or, if agreed with the client, then a further 50% discount on any further uses. In the database the main tables that I think I would need to create this function are the Project Table (one record is one project) where I would want to click on one radio button to turn on the discount on the second image and a further button to allow 75% discount (of original price) on all further usages. If the second button is not clicked (but the first is) then all further uses would just have a 50% discount. If neither button is clicked then all image fees remain at 100%. The second table is image sales (one record is one image sale) where all aspects of the sale are stored including the usage fee. I'm afraid I haven't got a clue where to start with this!?! I know it's a big ask but is it possible that anybody could help me with this?

       

      Yours humbly,

       

      Sean

        • 1. Re: Creating Discounts
          bcooney
             I'd approach it this way: Tables: Images, clients, Sales, SaleLIs (Sale Line Items) Clients are related to Sales by ClientID. Sales are related to SaleLIs by SaleID. SaleLI are related to Images by ImageID. Now, it seems you need to know how many times a client has used an image. So, by creating a self-join relationship from SaleLI to SaleLI by ClientID and ImageID, you can calc a "Previous Use" total. Once you have the Previous Use, you can determine what, if any, discounts to offer. You could create "smart" discount fields that calculate using the Previous Use number, or just enter a discount given the previous use. I see this as an exercise in calculations, rather than a scripting need.
          • 2. Re: Creating Discounts
            seanhunt
              

            Thanks bcooney for your advice. I'm new to Filemaker Pro and database building in general and am learing on the job so forgive me if I sound a bit dumb. I think I've managed to create a self-join relationship but have used ImageID and ProjectID (one client could have several jobs but would only get discount on the same job). I also do not have a separate Sale Line Items table as I create the invoice directly from the fields of the Image Sales table via a portal. However this is where I come unstuck! I'm not sure how I get the related info and then perform tasks on it. I've started to try creating a script that reads something like:

             

            If [Image Sales2::ProjectID = Image Sales::ProjectID and Image Sales2::ImageID = Image Sales::Image ID

             

            Then I get stuck!! I know it's not very far!

             

            Firstly, is this on the right track?

             

            Secondly how do I gain access to the results to perform calculations on them?

             

             

            • 3. Re: Creating Discounts
              bcooney
                

              Again, this isn't a job for a script, but rather a calculation. I don't see how you can set this up without the following tables: Client, Images, Projects, Sales, SaleLIs.

               

              On a saleLI, you need to specify a ProjectID. You can just format the ProjectID to be popup menu that uses a value list of ProjectIDs.

               

              Given that you now specify that a client only gets a discount if they reuse an image for the same project, your self-join relationship from SalesLI to SalesLI would include a match for three fields: ClientID, ProjectID, and ImageID.

               

               

              On the SalesLI record, which is in a portal on Sales, you would the calc field "PreviousUse", which would be the Count(salesli:salesli_selfjoin). This basically finds in SalesLI all records that have the same ClientID, ProjectID and ImageID, then counts them.

               

              Showing this number will let you determine the discount to give.

               

              I wish this forum supported attachments, then I'd provide a quick demo file. This is a bit advanced for a beginner, no doubt.