First a key detail you've asked about:
"I was wondering if I could make the client name the primary key between the two tables. "
That's not a good idea. In filemaker you should set up your primary key as an auto-entered serial number. Name fields (whether individuals or companies) are subject to change and also can be mistyped. If you link your data by a name field, such issues will create massive headaches for you.
Overall, it's difficult to comment in detail about your database design as it is the details that will make or break your project. Several of your descriptions suggest you might be attempting to treat your database as a spreadsheet instead of a database: "The starting point layout has tons of fields... ", "I have separate columns in the budget sheet for print, broadcast, and misc media types...." I could be mis-reading you, but if I'm right, you need to take a step back and view each row of data as a separate record and use list view, table view and/or portals to display these multiple rows of data. The resulting structure of your database could be much simpler and thus much easier to work with.
You might want to click the advanced search link and search for threads that deal with invoices to get some examples of how others have done this.
You're right about the spreadsheet thing since these forms were originally part of a spreadsheet program. I was asked to help automate the tedious parts of the process and came upon FileMaker as a decent solution.
I'm not sure I understand what you mean about viewing each row of data as a separate record. Each purchase order has several columns for information to be inserted into. Are you saying I should view each row of entry in those columns as separate records? Sorry, I know I'm coming across unexperienced.
Thanks for your quick reply.
That's exactly it. To translate a typical spreadsheet into a database table that can store the same data, treat each row as a different record and each column as a field you need to define for that record.
Try the following experiment.
Define three fields in a new table:
Leave Manage Database and Select Table View from the view menu.
Create and enter several names and addresses, using New Record to create new rows so you can type in additional data.
See how that looks quite a bit like a spreadsheet?
Yes that makes perfect sense. To use your example further, let's say Name, Address, and Phone are the same for several different types of orders. So on the same record with Name, Address, and Phone would I also have fields to fill in the various specifications for that purchase order? I think the design of this is starting to unfold in my head, I've just still got a few hitches to work out.
Remember that I mentioned searching the forum for Inovice threads?
A typical invoice in Filemaker consists of an invoice table where one record = 1 sales transaction to one customer and a second related table of line item records where one record = 1 type of item purchased on that invoice.
Thus, in an invoice like:
John Smith 2/16/2010
2 widgets $0.20 $0.40
3 Things $5.00 $15.00
The information shown in black is stored in an invoice record and the information shown in blue comprises two related records in the line itmes table. Typically, the line items are displayed in a portal which makes data entry easier. You can look up portals in filemaker help to learn more about them.
I'm starting to make headway thanks to your help. I was thinking too much in terms of spreadsheets to realize that the tutorial I went through would work for this project.
Anyway, right now I have a report with two sub summary areas that sort the layout by Client and Month. The problem is that it creates a new record for each Invoice. I want it to keep each invoice that contains the same client and month on one record. In other words, a summary sheet with several fields from each invoice for each client for each month. I tried using a portal, but to no avail.
I would like it to look like this:
Month, Year Current Date Company Logo
Media Dates Budgeted Amount
news 2-28 $8,000.00
news2 3-9 $4,000.00
Right now it would put news and news2 in separate records. Any ideas?
This certainly can be done, but I'm not sure I can interpret your table structure correctly from your last example.
news 2-28 $8,000.00
news2 3-9 $4,000.00
Represent two invoices or two items on the same invoice?
In either case, I put together a tutorial for making a summary report based on a simplified invoice. Take a look at it and see if it helps you figure out how to do what you need:
I want those items to represent separate invoices, which would have different ID numbers.
Would portals still be the way to go there? I couldn't get them to cooperate.
I wouldn't use portals for this. It can be made to work, but is less flexible.
Did you check out the link I posted? A variation of that summary report will produce what you want. Just base your layout on invoices instead of line items.
Yeah I looked at that link. I based the layout on the Invoice ID instead of line items but it still shows one record per line item. I'm trying to get each record to hold every invoice from the entire month for one client.
I need to be able to print that one record as a budget sheet for that month. The next record would be a different client, but maybe the same month. Does that make sense?
Go back and check the variations at the end at the end of the tutorial .
You can set up a sub summary part that groups your records by month. You can place invoice summary fields in this layout part and then delete the body part entirely. That will merge all your invoices of the same month for a given customer into a single line.
A simple calculation field you can use to group your records by month:
cMonth : InvoiceDate - Day (invoiceDate) + 1
Set this calculation to return a date.
The date returned will be the date for the first day of the month for that invoice so you can easily sort it by this month and even format it to display only the month and year if you want to put it in your report.
Thank you very much for your patience with me, but I must not be communicating what I want very well.
I've gotten the records to sort by client and month, but instead of three records that have the same client and month showing different invoices I want one record that shows the client and month with several invoices. Right now I have 12 records sorted by month and client because I have 12 invoices, but I need 4 records with three invoices of the same client and month showing.
Everything I try to do I seem to still end up with 12 records because of 12 invoices when I really just want 4 with the same information as those 12.
What parts show in layout mode on your report layout? It sounds like the last line of your report is the Body instead of a sub-summary part.
Create a sub-summary part "when sorted by" cMonth.
Place cMonth and a summary field that totals up your budgeted amount in this part.
Delete the body part.
Enter browse mode and sort in a sort order that includes cMonth (Sort by Client, then cMonth)
That should do what you need.
The cMonth calculation returned 2010 as the year instead of month. Not sure what I did wrong there.
Here is a screenshot of the sub summary report in layout mode which yields twelve different records.
::Month is a typed in month name which I was using for testing purposes. If cMonth can be used to calculate the month that would be nice to convert that number to text.