Getting Active Fields in one

[_Kimmie_]

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!

[LaRetta]

Hi Kim, welcome to FM Forums!

"I was wondering if anyone had a better idea?"

Yes, re-design it now. Those 80 product fields should be product records (in a Products table) and probably consist of an Invoice & LineItems table as well. Do it now before your solution becomes more complex and before you've wasted countless hours not getting the results you need with your existing structure. If you would like some examples, we can provide them but it would help to have more information first.

UPDATE: BTW, you can use one field with checkbox. However, you will not be able to produce grouped reports such as:

Product A

...ABC Company

...XYZ Company

ProductB

... etc

Edited December 22, 2009 by Guest
Added update

[Søren Dyhr]

However, you will not be able to produce grouped reports such as:

Well not entirely true, although I indeed agree with the move to records instead of fields!

http://fmforums.com/forum/showtopic.php?tid/183639/fromsearch/1/hl/survey%7Cbruce/tp/0/all/1/

--sd

[_Kimmie_]

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!

Edited December 23, 2009 by Guest

[LaRetta]

Also, I have to keep these products grouped in 16 separate categories. How would I do that in a table?

Yes, using relationship and relating them is way to go (similar to an invoice/lineitems/products structure).

I would suggest that you have a field in your Product table called Category. You can then either:

1) use a Custom Value List where you type these 16 categories to allow assigning to each product or better still,

2) create a table which holds an auto-enter serial and the category name - then select the category ID for insertion into the Product table Category field. Why use an ID instead of the name? Well, life has a way of changing things on you. If you insert the Category name and the Owner decides they want to call one category 'excempt' instead of 'tax-free', you can just change the name within the Category table and it will change throughout your solution. If you insert the Category name throughout your solution, you must manually change this data.

[_Kimmie_]

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.

[LaRetta]

Check out the sample file here Invoices demo by Comment . It will show you how to properly structure and how to use the IDs to join your relationships.

Then go to File > Manage > Value Lists and see the Products value list. You would create a new table called Categories (with an auto-enter CategoryID serial) and create a new Category value list. Then, just as on the Invoices layout, if you click the ProductID in the portal, it will open the Products value list, you would attach your new Category value list to your Products CategoryID field (in your Products table) for assigning the category to each of your products.

Well, I'm giving you a lot of information so play with it and let us know as you have questions. There are many wonderful people here willing to assist and remember that we ALL were new once so you aren't alone. :smile2:

[_Kimmie_]

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!!