Count on Multi Fields

[Delights]

Greeetings,

I have a Table Called "Products" and Another table called "Categories". They are relationship together and in the Table Categories there is a field called Count.

Now count will go and count the amount of records that are in the Field "Category_1", however there is another field called "Category_2".

"Category_1" will have a the following enteries,

Books

Lingerie

Vibrators

and "Category_2" will also have some fields that expand on that, so for example Category_1 might have Lingerie, Category_2 would then have things like Babydolls, PVC, Unisex, etc.

Atm the count only runs off the category_1, it is causing it to duplicate some of the counts, for example Lingerie has about 30,000 products instead of about 1000.

Is there a way to make it so that if there is a field in Cateogy_2 then you take that into the count, otherwise only count Category_1.

I tried to use the following code, but it is still repeating the fields.

If(IsEmpty(Product Catalog::v_categories_name_2);Count ( Product Catalog::v_categories_name_1 );Count ( Product Catalog::v_categories_name_2 ))

[mr_vodka]

Create a calc field that concatenates the two fields and then count.

cBothCatg

category 1 & Case ( not IsEmpty ( category 2 ); "_" & category 2 )

P.S. IF you have the same exact value in category 1 and category 2 you will have to tweak your calc based on your business rule.

Edited October 6, 2008 by Guest
added PS

[Delights]

Greetings;

I made a field that has the names put together, so for example Vibrators / Assorted, becomes Vibrators_Assorted,

This allows me to use the count and get it working, below is the coding incase anyone else wants it..

If ( IsEmpty ( Product Catalog::Count Name2 ) ;Count ( Product Catalog::Count Name1 );Count ( Product Catalog::Count Name2 ))

Thanks for the help.. :