You definitely do not want separate tables for invoices based on which show they were at. Just use a field in Invoices called "Show" so that you can report on them individually. They're storing the same type of info (sales), so there's no reason to put them in different tables. Worried about forgetting to fill the show name in on the invoice each time? Just use a script to prompt for the show name whenever the software is opened - and set a global field that contains the show name, and auto-enter that into invoices.
Since a customer/owner can have more than one dog, these are two separate tables. But I'm guessing a dog can't have more than one owner. That makes it fairly simple - just put a portal in the customer table to allow easy entry of dogs for each customer and "allow creation of related records".
When you create an invoice, it should first be related to a customer with a customer id number. A second key field in invoices could be used to indicate the dog. This can be setup as a popup menu that lists the customer's dogs. Use a value list that gets the related dog id numbers and use the "also show values from second field" option to show the dog name (you can also "show only values from second field" to remove the id number from view, even though this is what gets stored in the field when you choose a dog name).
If an invoices can have multiple dogs, you need to add another table which is normally called "line items", and put the above popup menu in the line items with a portal (and set up an additional relationship between line items and dogs.
Your relationship graph will be a straight line that looks something like this:
Dogs - Customers - Invoices - Line Items - Dogs For Line Items
Where dogs related to customers with customer id, customers relate to invoices with customer id, invoices relate to line items with invoice id, and line items relates to an additional dogs table with dog id.
THanks jason. I'll have to do some learning further on the scripts to figure out how to prompt me when opened but I can't imagine that it is too difficult.
Yes there could be multiple dogs per invoice, but my question is where do products go on the graph?
Here is a possible scenario:
John has two dogs, Ralph and Marty.
At a particular show he wants to order a Jumpers video of Ralph to be put on a flash drive and a Standard video of Marty on the flash drive AND a DVD. He also needs to buy a flash drive from us to put the videos on.
So essentially there are 5 different line items and 3 different products, video on flash drive, video on DVD, and the flash drive itself.
Products would also relate to line items based on product id
In FMP12, you set the open script using File Options > Script Triggers > OnFirstWindowOpen
In earlier versions, it's on the first screen you see when you go to File Options.
The script would show a custom dialog box requesting the current Show name, and put it in a global field. It doesn't really matter what table the global field is in... global fields are accessible from anywhere. I'd put it either in Invoices or in a separate "preferences" table that is only used for these types of things. Then your invoices table would have an event field - set it to auto-enter a calculated value (the global field).
You could also just put the global field on a layout and avoid the dialog box and startup script - just have to remember to change it as needed.
When I try and set up an additional relationship between dog id and line item it tells me i can't. so i need to create a second dog table?
Not a second table, just a second table occurence. That's what I meant by "dogs for line items".
That may have been what you meant.
Dogs needs to appear on the table in two places because it relates to the others in two different ways.
ok so i have the dogId line item and the drop down in the portal, the customers dogs show up when i click on the drop down, but when i select one it doesn't show up on the line item. am i missing something?
THanks again for all the help!
I'm not sure what you mean. Lets review.
The portal should be on an Invoices layout, and the portal should relate to line items. In the settings for the relationship between invoices and line items, "allow creation of related records via this relationship" should be enabled under "line items".
Line items should have a field called "dog id", and it is based on this field that there should be a relationship to "Dogs for line items" (2nd table occurence of "Dogs")
The "dog id" field should be placed on the first line of the portal in layout mode. Format it as a popup menu and assign a value list that shows "dog id" from the original Dogs table (the occurence related to customers), and it should be set to show the additional field "dog name".
Now if you have an invoice that is related to customers, and you click in the "dog id" popup menu, then choose one of the customer's dogs, it should create a new line item. If you have other fields in line items that perform lookups from the "Dogs for line items" table, this information should appear also.
An additional popup menu could allow you to choose a product for the line item.
Ah thanks. I had the dog id showing values from the dogs from line items occurence. Now it works.
I think I'm pretty much there. is there a way to create tabs within the browse mode? let say I want a tab on the invoice for each dog so that their orders are on the same invoice, but grouped together? but I don't know how many dogs some one might order for so I could add them. kind of like adding a line item?
Not really, but portals can be sorted and/or they can be filtered (double click the portal to add a filter).
You can add a popup menu that lists all dogs on the invoice, (based on a new value list that takes the dog name field from related line items - a field you may not have at this time, but you can add it to line items and set it to be an auto-enter lookup that will be performed when a dog is selected).
Your filter would then be set to show line items where [selection field in invoices] = [dog name in line items]
You may need to put a script trigger on the popup menu in invoices, connected to a script that simply refreshes the screen whenever the field is modified.
On your printing layout, you can group and sub-total each dog if you like (using leading and trailing sub-summary parts & appropriate sorting).
i should have figured that one out... sorting will work fine. thanks.
ok so i have everything pretty much set up how i want it, but now i can't figure out how to delete a line from an invoice if the customer changes their mind.
also is there a way to script in a discount on items if a certain amount are ordered. for example as i am adding items into the line items, and i have 5 lines that are all the same item(so same price), but different details (day, level, etc) then the price would drop for all of them to a discounted price?