Search does not find values coming from lookup tables

Flipper,

what do you use as a database - Access, SQL or ...?

Philip

Hi Philip
I am using Access 2000
regards
flipper

Hi Flipper,
Ok, I'm using in my projects Acces as well. For your purpose I would set up the 4 tables as you did. For table 5 I would set up in Acces a Query (not a table) holding all fields necessary from the 4 tables. This query you can use in ASPRunner.
Philip

Hi Philip
thanks for trying to help. I appreciate it !
While trying to set up a view I found the problem or let's say I have found the solution.
In the look up table I have linked to the ID, but if I link to the wanted field directly it works!
So in my Combination table now I do not have numeric values (foreign keys of other tables) but I have text values.
I don't know, whether it is supposed to be like that or whether it is a bug of ASPRunner.
Anyway, thanks a lot again for your help.
kind regards
flipper

Hi,
you can do the following:

1. edit your SQL query for tblCombination table: join all tables in this query. You can use Query Designer for this purpose.
   
   Here is a sample:
   

   select tblCombination.field1,
   

   
   tblCombination.field2,
   
   tblMachine.field3
   
   from tblCombination inner join tblMachine
   
   on (tblCombination.fieldID=tblMachine.fieldID)

2. Then check off field3 on the list and search pages only on the Choose fields tab.

1. edit your SQL query for tblCombination table: join all tables in this query. You can use Query Designer for this purpose.

It is a lot of extra work to manually join all tables that foreign keys are referring to.
My opinion is that this should be handled automatically by the Lookup Wizard/table.
I get different behaviour for the search on the list page and the advanced search page when I configure a foreign key as a Lookup table and set the link field and display field to appropriate values (and also enable ajax popup). The advanced search works fine and gives me search suggestions from the configured display field in the lookup table. The search on the list page, however, works the other way. It gives search suggestions based on the link field. I can hardly believe that this is the intended behaviour.
Mikael

Mikael,
you can easily (with some clicks) join all tables using Query Designer on the Edit SQL query tab.

you can easily (with some clicks) join all tables using Query Designer on the Edit SQL query tab.

Don't know if I have missed something but the only way to join tables using the Query Designer is to type the joins by hand. I have lots of tables and some tables have more than 20 foreign keys, that means a lot of manual typing.
I still dont understand why the search on the list page, and search on advanced search page behaves differently. Could you please give an explanation to that? If search on the list page behaved like the advanced search, then I wouldn't have to type the join in the query designer.
Mikael

Mikael,
to create a joined SQL query proceed to Visual Editor, add tables to the diagram and drag-n-drop field from one table to field in another table.

It works exactly the same way it works in MS Access.
On advanced search page you select a value from dropdown box. ASPRunnerPro searches for ID (Link field) while you think it searches for the Display field.
I hope this makes sense.

On advanced search page you select a value from dropdown box. ASPRunnerPro searches for ID (Link field) while you think it searches for the Display field.

Yes, all this makes sense and I fully understand how the advanced search works. So i assume that due to technical reasons is the list search is not implemented in the same way.