Multi Table lookup fields

(1) Share

Report

Posted on by Abdullah_Ahmed

114

I have a form with 2 datasources QuotationTable and QuotationLine.. QuotationLine has a field called ItemId related to InventTable. I want to override the lookup for ItemId

so, I tried to override lookup method of itemId field in QuotationLine but it gave me the error that 2 lookup forms trying to open! So I override the event handler of ItemNumber control in the grid.

- the sysTableLookup has QuotationLine as a parameter but it isn't used as qbds below.

- I tried to have Quotation line as qbds and then link qbdsInventTable with it and I get the error: "Attempted to read or write protected memory. This is often an indication that other memory is corrupt"

- the error I get: 'Failed to execute query because no root data source on the form matches the root datasource on the query.'

I have the same question (0)

All responses (10)

Answers (0)

Layan Jwei 8,097 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

SysTableLookup doesn't support adding fields from multiple tables.

You could create a View with all of those joins and use it with sysTableLookup

Thanks,

Layan Jweihan

Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

Hi Abdullah,

As mentioned, you can create a view and consolidate all the tables there.

One more option is to check whether there is any method available in InventTable to fetch ProductName and ItemGroupId values. If the method is available, you can use lookupTable.addLookUpMethod(tableMethodStr(TableName,MethodName) to mention the table and method name.

Thanks,

Girish S.

Was this reply helpful? Yes No
Waed Ayyad 9,039 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

As mentioned, you should either create a view or create display method and use it in your lookup.

Thanks

Waed Ayyad

Was this reply helpful? Yes No
Abdullah_Ahmed 114 on at

Like (2)

Report

Thank you all for your answers.. I tried the view and it worked but has a problem! there are many duplicates in data and I don't know why.. I tried to create unique index on itemId but it doesn't reflect on the database and I can't find the index in the view in sql server.

I found the methods in InventTable so I used them instead and it's working fine.. but I wanna figure out what's happening with the view!

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

If you find any duplicates maybe you missed the relation between the tables, you added as DataSource in view.

Thanks,

Girish S.

Was this reply helpful? Yes No
Abdullah_Ahmed 114 on at

Like (0)

Report

I didn't forget to set UseRelations to yes and I still have the problem!

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

I think EcoResProductTranslation has LanguageId field, please try setting that language id and check.

Thanks,

Girish S.

Was this reply helpful? Yes No
Abdullah_Ahmed 114 on at

Like (0)

Report

This is the view now.. I can't add relations manually so I just set UseRelations to yes in both tables (InventItemGroupItem and EcoResProduct)...

- InventItemGroupItem should relate to InventTable by ItemId field

- EcoResProductTranslations should relate to InventTable by Product field

- The index is unique and it didn't help either!

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

Can you tell me why you are unable to add the relations manually?

Use relations will work only if there is a relation added between the tables at table level.

If there is no relation added between the tables, then you need to add the relation manually and mention the related fields. You won't get unique values unless you correct the relation between the tables.

Thanks,

Girish S.

Was this reply helpful? Yes No
Abdullah_Ahmed 114 on at

Like (1)

Report

I wasn't able to add relation manually because I set UseRelations to Yes.. once I set it to no I added relations manually and added a range on InventTable DataAreaId field and set the value to curExt().

This is working now and there are no duplicates at all.

Was this reply helpful? Yes No