Title
newbie relationship help.
Post
I'm so confused. I have an erd that is exactly like the filemaker lessons. I except I want to add PROMO to mine. items the company gives away.
How do I connect them? Do I need another customers table and another products and line items table? right now I have one table of each. The promo will show who they gave the items to, the items & the price of the items. I have FM11
Will you sometimes make an invoice with a few products and a few Promo Items?
Or with only promo items?
If there are only promo items I guess you wouldn't need to have an invoice number.
But other than that I think we could solve this in an extremely easy way.
The only things we need are:
- one extra text field in the Line Items table called "Promo"
- A change to the calculation that calculates the total in the Line Items table so the total for a promo items = $ 0,00
- An optional "Promo" message on the printed invoice to show which items are promo items.
Does that sound like a plan?
Ok, so first: create a new text field in the line items table. Call it Promo.
Then add this field to the line items portal on your layout. Make it a checkbox that's only like 18 x 18 pixels so as to only show the checkbox itself, and not the label. Make it a checkbox with one manual value of "Yes"
Create a label on top of the portal, name it "Promo". I'm guessing you already have labels there for "Products", "Amount", "Price, ...
Then change the calculation of the total.
I'm guessing now you have something like "Quantity * Price"
Let's make than an IF statement.
The idea is that when the field "Promo" contains the value "Yes" this item is a promo item and therefore free. ($ 0,00 )
So the IF statement (of the top of my head) should look something like:
IF ( LineItems::Promo = "Yes" ; "" ; Quantity * Price )
That means that if the value in the field Promo is "Yes" the calculation result will be "nothing" or Empty.
Otherwise the calculation does what it should normally do.
Ok, test this part, it should work pretty nicely already.
Now go to the Invoice Print layout and add a text there where the total price of a product would normally be.(Right on top of the field "Total")
Write the text "Promo" there.
Then right click on this text and select "Conditional Formatting". Under Condition it needs to say "Formula". The formula would be something like:
IsEmpty ( LineItems::Promo )
This formula will do some conditional formatting if the Promo field is empty, in other words, if an item is not a promo item.
On this condition we want the word Promo to disappear. We don't have that option, but we can use a trick.
Choose "More Formatting" and set a custom font size of 500.
This will make the word promo so large it becomes invisible.
There. Try this out by adding some normal and some promo items and checking your print invoice in Preview mode.
Let me know if you run into any problems. (This is all of the top of my head)