What type of field to use?

[Robert Collins]

I have been using Filemaker for some time now but this has me stumped. I have tried to keep this as brief as possible.

My database is used for sales invoices for my mobile phone shop.and I use a traditional sales invoice table along with sales line items and product tables.Either every year or 18 months, a contract customer will get a discount off their next phone upgrade. I have a field in 'sales line items' that holds this.I have also set up a layout and script that prints out a letter showing the handsets a customer is entitled to (based on their discount) with an image using portal from products - this works fine. I now want to show all the handsets in the range but either showing "FREE" or the money difference between the customers discount and the retail price. I have created a new relationship which brings up all the handsets but I can't seem to find a way of getting the (if any) cost difference below the image of the handset. the nearest I can get is using a tooltip with the following criteria:

If (Display upgrade options for customer::Retail price of phone ≤ Sales line items::confirmed discount on next upgrade ; "FREE" ; "The customer would have to pay £" & Display upgrade options for customer::Retail price of phone - Sales line items::confirmed discount on next upgrade)

this works great but when I put this in calc field it puts the same amount to pay under every image.

Any ideas?

thanks in advance

[comment]

If I understand this correctly, you need to add a global field to the Products table, either to to identify the selected customer (and enable a relationship to the relevant discount), or to hold the selected customer's discount directly.

Either way, you can print a report for only one selected customer at a time. Setting the global should be part of your script.

[Robert Collins]

thanks for the reply.

I don't think the discount can be global for the customer account as they may have more than one mobile phone and therefore more than one discount active.

Sorry if i'm missing your point

[Robert Collins]

Sorry, I re-read your post and now see what you are suggesting.

Create a global field in Products

in the script to print the letters out, use a SetField command to get the discount into the global field.

I shall try that now, Thanks

[comment]

I'm afraid I cannot be more specific without more details from you. But clearly, when you print from the Products table (as it seems you do, and as you should), all the customers look the same. To make the calculation from the point-of-view of Products, a specific customer must be singled out by a relationship, or a specific discount must be passed to the products table.

EDIT:

Yes, something like that. Of course, if it's the discount itself, the global can be in any table.

Edited June 30, 2008 by Guest

[Robert Collins]

Sorry, I re-read your post and now see what you are suggesting.

Create a global field in Products

in the script to print the letters out, use a SetField command to get the discount into the global field.

I shall try that now, Thanks

Just tried it and it works. The reason I initialy didn't think it would was because in addition to printing the letters, I wanted to have the information on screen for the sales people to see and I was concerned that if 3 people were using that feature with different customers and only one global field, it would show wrong information - But it doesn't , so I have no need to worry.

thanks again

[Tim W]

I was concerned that if 3 people were using that feature with different customers and only one global field, it would show wrong information - But it doesn't , so I have no need to worry.

In a multi-user environment (shared file) each user has his/her own global value. Just a thought to bear in mind.

HTH,

Tim