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.
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?
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.