Hi here is a challenge for you:

I am trying to create a type of lookup in my report. To use as code inside a formula (can I mix crystal and VB script in the same report)?

I have a main SQL that reads all records and is used for the main detail. However I have another table I wish to use to group the types of results into different buckets depending on the records category.

To attempt to explain more simply imagine I have a restaurant, you can have either 1 of 3 options of a Starter, Main, or Desert. Call this lookup table X. Then depending on which of these options you select you either get a Soup, Steak, or a Jelly lookup table Y.

First of all I need to load these lookup tables when the report first opens.So I have Lookup table X containing ‘STARTER,’MAIN’,’DESERT’. And lookup table Y = ‘SOUP’,’STEAK’,’JELLY’. They each havean index? Value 1,2, and 3.

When the user runs the report they select which course they want, eg MAIN

Next I create another formula that uses these lookups.

Imagine I am processing each record from a stock /inventory table and depending on the user parameter it will check if MAIN course (which will be STEAK) is in the inventory table.

So my formula may look like the below (note this is no syntax but something that could exist):

If TABLE_INVENTORY.ITEM = (item from lookup Y index (table X = Parmater1) then ‘YES’.

So since I selected MAIN as my parameter it check to see if there is STEAK in the inventory.

I hope this makes sense and if you can suggest how I might go about this in Crystal would be very useful.

Thanks,

JD.