Help creating a new relationship?

[_Kimmie_]

Hi All,

I am not 'new' to filemaker but have had no success tying in two tables. I have just created a one table database for a magazine that comes out every other month. Additionally, the magazine also sells many different items that have different submission dates. Example, website sponsors every 4 week minimum, enewsletters every week, etc. The database is built to hold 4 years of data as one contract can hold 2 years. I now have to build the invoicing (all items invoiced differently) and detailed reporting. I know i can do this more efficiently if i build separate tables for these two things but am stumped on how to start. Each company holds a control number and each item is date driven. If i can get some help on a start and maybe see how it works I think I can follow it...I think . LOL. Anyway, I am self taught so my scripts and calculations are simple. Any suggestions?

Thank you!

Kim

[AudioFreak]

I set up a simple Invoice Relationship file that helped someone else understand. here

Let me know if that helps. If you have questions after looking at it, fire away.

Michael

[_Kimmie_]

Hi Michael,

I looked at your invoice db and thank you for sharing that with me. I see what you did and i think if i re-arrange my fields a bit. Meaning right now EVERYTHING is in one table. Most of the fields are sales items so i can easily create a contacts table. The only thing that confuses me is that i have sooo many 'line items' that are all separate fields. Some are from date - to date driven. Some are cut and dry and all are invoiced separately. In your sample db, you only have one line item. If i have all items in separate fields, how would i tie that in? Say the company bought ads for Jan/Feb, Mar/Apr, May/Jun and 2 website sponsors? How would i list those things together? Second big question is how would i create a report stating the sales items as the category and list the companies with their information under each category? I feel like i am talking in a circle? BTW my husband is a guitar player too and your right!

Thanks for the help!

Kim

[AudioFreak]

Hi Michael,

How would i list those things together?

Kim

You'll notice the portal has a blank row. That empty row below the one item is for adding another item. They are tied using the Po Number relationship. Can add as many items as you wish. Everytime you add another item a new blank row will appear allowing you to add another item.

Second big question is how would i create a report stating the sales items as the category and list the companies with their information under each category? Kim

Not sure what you mean with this one. Little more detail and I'm sure I can help you out.

[_Kimmie_]

Hi Michael,

Ok, I can understand that. Most importantly is the reports. I need to show per sale item, the list of companies that had purchaed the item for that particular time period. Say ACME was a company that purchaed a full page color ad for Jan/Feb a half page ad for Mar/Apr and a website sponsorship for the weeks of 6/1/2007 - 6/30/2007. When i run a weekly report, they want each category broken down. Magazine ads for Jul/Aug: show list of companies and what they purchased, then web sponsor category with the weeks they purchased and listing all companies. I am not sure if I can get that in one report?

Thanks,

Kim

[AudioFreak]

Look into SubSummary Reports once you have everything set up. You can get what you want using them. I'm not great using them but can probably help get you going or one of the gurus here can help.

Michael

[_Kimmie_]

Hi,

I tried that and it was a mess! Thank you for your help, it was appreciated. Since you play guitar, my husband belongs to this amazing forum called The Gear Page. You may want to check it out. These guys are serious about their playing and their gear. I know, its all about the tone! His nickname is Mass. Take care!

Thanks again!

Kim

[AudioFreak]

LOL.....Subsummary reports can be tough to get set up right. It's a matter of getting the fields into the correct Part of the layout. And getting the Parts set up correctly.

If you want to post your file I can give you a hand.

Nice I will check out those forums. If he's into gear for recording have him check out GearSlutz. I work for a company that makes HI End gear for recording studios.....Hence the name AudioFreak :o