_Kimmie_

WOW!! I closed FM and re-opened and it all works!! ODD?!? THANK YOU LARETTA!! :)

Hi LaRetta, The merge fields was an awful idea but I was desperate! LOL Ok I have rearranged my thinking and added my products to another linked database I use for sales. When I go to a company record, I can put the year of the sale which will allow me to choose from a dropdown list from another database that just lists the items for that year. I added my products to that database figuring since I am linked it would be the best way to do it. So I set up my fields in a new table in my main database and I am running into a problem. I can't get JUST the products to show, it lists EVERYTHING. When I try to tweak the value list to show just the second field, it only shows me 11 records. I keep changing the relationship and value lists but I can't seem to get it right. I know this is difficult without showing you the database but I unfortunately can't post it. Let me try to break it down to better understand: I have a MAIN database that is linked to a SALES database. The SALES database holds the sales ID, year, description of sale, pricing, etc. I added two more fields, division and product since these are the only two things I need to track. I added a new table in my MAIN database that's called products. I joined the products table (MAIN) to my contact table (MAIN), SALES database (sales ID to product ID in my products table), and I created a second relationship table called SALES 2 to link the division field to the division field in my MAIN db. Now I formatted my fields in the MAIN db to "lookup" in the SALES db as well as created the value lists. The value list works fine when I click on the correct product, it then populates the division number and product name as I need. I want to list ONLY the products and NOT the ENTIRE database which it's doing now. If I change the value list to say show only the second field value, it only shows me 11 records, if I take that tick off, it shows me everything. This information is permanent not driven by a year. LOST :)

Hi All and HAPPY NEW YEAR! I have multiple merge fields I need to add to a document I created in FM. BUT not all of the merge fields will return text, nor should they. My problem is I am trying to create a calculation that will show the merge fields with text and add a comma and proper spacing if there are more than one. I found this on another Filemaker site: Example: Create a database with the following fields: Address (Text) Suite (Text) City (Text) State (Text) Zip (Text) AddressSpace (Calculation,Text) = If(IsEmpty(Address) and IsEmpty(Suite), "", " ") CSZCommaSpace (Calculation,Text) = If(IsEmpty(City), "",^", ") CSZSpace (Calculation,Text) = If(IsEmpty(City) and IsEmpty(State), "", " ") Then insert the Merge fields in to the text object to look like this: < ><><> <><><><><> Enter any data you want. The space will appear between Address and Suite only if both values are not empty. The comma will appear after the City if City is not empty and the space will appear before the Zip as long as both City and State are not empty. I tried using the commaspace calculation above but get an error highlighting the carat(^) saying that "a number, text constant, field name, or "(" is expected here" I am obviously missing something but I don't know what? Here is how I am trying to list certain products: Division 1: <> <>, etc. (up to 10 products per Division) Can anyone help? Thanks!

Thank you so much! I will hopefully be able to test this out today. I want to wish you and your family a VERY HAPPY HOLIDAY!!

Hi LaRetta, Thank you for your help and since I haven't built the table yet, I should probably go with your second suggestion. I have built this database by the seat of my pants, LOL. So when I create the table, I use the ID to relate to a category? Little confused but what else is new.

Thank you both for your time in trying to help. I didn't make the products a table as they are not billable items but it makes more sense to add them as a table. Since these items have to tie into the Companies, all I need to do is create the table with the products, create a co id to link to main contact table and voila! Correct? Also, I have to keep these products grouped in 16 separate categories. How would I do that in a table? Thanks again!

Hi All, I have a list of 80 products each product in a separate field. My database consists of customers that each have to choose a handful of products for their companies so each contact record contains what they have chosen. Radio tagged per record. Now I am trying to create a summary sheet per company and would like to include the products they have chosen to be listed on this summary sheet. I have tried every calculation I thought it may be to get this information into one field. If I use the insert merge field it blows my document on two pages. I tried to create a calculation using "Case" but it would only read the first field chosen. Before I rewrite the entire thing to one field with a check list which would give me a lot of manual work to do. I was wondering if anyone had a better idea? I appreciate your help!

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

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

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

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

I apppreciate your help and thank you for welcoming me to the forum! Unfortunately, the DB is already built and my time is up to re-create the entire DB. I was hoping there was a way to query the dates? Thanks again!

Hi All, I am creating a DB for a magazine. The DB consists of 12 months per year for 4 years. A company may have purchased certain issues from mid year to mid year on the same contract. I need to generate a unique invoice number per contract (companies can have multiple contracts in one year) and I think if I use the contract date as my trigger I can use a calculation. The problem is I have the dates for each year as one repeating field per year to input contract date. How would I write the calcualtion to look for a group of specific dates, per record in a repeating field to generate a unique invoice number? I am self taught on FM, I have been working with it for a few years. Any help would be appreciated! Thank you for your time. Kim