if statement to create consolidated selection but retain original values

[#1]

Hi,

Please can you help. I have a field called 'Site' in a 'Customer Order' table that contains a list of countries e.g.:

Denmark

Site
France
Great Britain
Belgium
Spain
Japan

I need to apply an inter company rule to the above so that Belgium and Spain report under France. I need to create a France Consolidated selection within this field but also retain Belgium and Spain as separate selections within the 'Site' field.

Using this 'Site' field I can easily create the Consolidated site using the following if statement. However I then loose 'Spain' and 'Belgium' as a selection.

if(Match(Site,'Spain', 'Belgium'),'France Consolidated', Site) as [Site Consolidated]

Please can advise how I create a consolidated selection but retain the original selections?

Thanks

[Oleg_Troyansky]

If you create Consolidated Site like you planned, you can then use a Drill-Down group for the selections. Then, if you select "France Consolidated", you'll be able to make a further selection from the field Site.

Alternatively, you can look into hierarchy loads (using HIERARCHY and HIERARCHYBELONGSTO), but the logic of that load is way to complex to describe here - look it up in the manual. The hierarchy load, when performed correctly, can help you select any of your levels of Sites from a single List Box.

cheers,

Oleg Troyansky