newbie relationship help.
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
That would depend on how you are adding promo items.
Are you just adding them to your invoice in your LineItems portal?
Would you be able to place Promo items in your Products table? Maybe with an additional field to mark a product as a "Promo Item"
These items would not have a price. And could maybe be marked as "Promo" item.
Or are your Promo items normal products that you would like to mark as "Promo Item" on your invoice?
So the price goes away after you mark an item to be a "Promo" item.
This way you also don't even need the Promo table.
Another way (using the promo table) is to add a "PromoIdFk" number field to your LineItems table (Like the ProductIdFk field)
FK stands for "Foreign Key" and is the place where you store the ID of, in this case the promo item.
Then make this relationship:
LineItems::PromoIdFk----------[=]-----------Promo::ID
This is exactly the same as with the Products table.
Then you make the PromoIdFk field in the LineItems table (on the portal) a dropdown that get's it's values from the Promo table. First field ID, second field Promo Name or something.
Let me know if you can't figure it out.
The products that will be given away as promo items come from the products already entered. I was thinking that I may NOT need a promo table either, but I'm not sure how to go about it.
My initial thought was to make a portal and have the items be selected from a list just like I am doing with the invoices. I already did the invoices section, when I click in a field (customerid) the customers name pops up the user selects it, then goes into the products portal and selects the products.
I was thinking of doing the same with promo items. select a customer, them select the products. Then I got confused.
actually, your reply makes me not feel so stupid, because I have thought of a few things you suggested, I am just not sure which way to do it or how to do it.
Thank you so much for your help and making me not feel so much like a newbie. :)
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)
hi!
Will you sometimes make an invoice with a few products and a few Promo Items?
No. Promo is more for the owner to keep track of what he's giving away.
off to go try what you've written. Thanks! you are DaSaint.
Thank you so much! This worked, I may come back and have more questions. Thanks again
Ok, so now, what if when the user clicks the checkbox saying it's a promo item how do I tell filemaker to create a record in my promo list items? I.E. put the contact(the person the item was given too), the item, the date (promo was checked), and the items given in another portal in my promo list items? Does that make sense? Promo list items is just a place where they can see all the stuff they have given away.
You know how on the invoices, when I select my customer and products it creates a record in line items with related information. How can I have it do that when I click the checkbox? With a script?
sorry if I am being a pain...
These promo items are in the line items table now. The only difference between a Promo item and a normal item is the checked box "Promo".
You can display all promo items given to a client in a portal on the client layout.
Or you could make a list view based on LineItems displaying all promo items that have been given away by performing a find for records where the Promo field is checked.
You could also create a subsummary part when sorted by ClientId So you would have something like:
Client 1
PromoItem 1
PromoItem 2
Client 2
PromoItem 3
...
I made you a very simple example here:
http://dl.dropbox.com/u/18099008/Demo_Files/Invoice_PromoItems_DaSaint.fp7
Check out the ListOrderItems layout.
Thank you so much! You just made a light bulb go off. I added a promo tab to my tabs in the contact layout and a subsummary view. That example was just awesome. It looks nice too. I also want to thank you again for the original answer in this thread. It all worked great and a extra shout out for doing it off the top of your head. When I read that the first time I saw it, I thought 'it's not going to work,' well it sure did!
OK, so now I need to add a receipts section to the above. The receipts are for the items the company buys to sell. I'll be pulling over the contact info(the person they bought the item from) and the product info from products and adding fields like if it's a tax write off etc... I'm confused about how to relate this new receipt table to the rest. any help would be great.
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)