Solved

Setup table relationships for lookup

  • 16 April 2024
  • 4 replies
  • 46 views
JuniSihombing
Rank
Userlevel 4
Badge +11

Hi all,

anyone has an idea how to setup table relationships for 

PART_NEED.PLACE_ID_FROM lookup in DebriefPartNeeds.

 

The lookup should show:

place.place_id

place.name

location.location

location.description

 

the filter should be:

PERSON_PLACE.place_relationship like 'C_ORDER_FROM%'

 

How to setup the connection of PERSON_PLACE to PERSON AND LOCATION?

 

The lookup in PART_NEED.PLACE_ID_FROM should show these 2 places/locations (in the box)

 

 This is the setup I have done.

 

 
 

This PERSON_PLACE and LOCATION relation maybe wrong.  :-)
How to setup the correct one?
 

The filter:

 

 

Cheers,

~Juni

 

 

icon

Best answer by Shneor Cheshin 18 April 2024, 01:57

View original

4 replies

Shneor Cheshin
Rank
Userlevel 6
Badge +26

Hi @JuniSihombing 

What is the issue?

Can you click on the ‘Validate’ button and see if the query makes sense?

Cheers!

JuniSihombing
Rank
Userlevel 4
Badge +11

Hi @Shneor Cheshin,

 

the issue is the lookup return 0 record.

 

The validate returns this statement:

See the yellow highlighted line.

select location.location, location.description, place.place_id, place.name, person_place.place_id from person_place left join place on person_place.place_id=place.place_id left join location on person_place.place_id,location=location.place_id,location where person_place.place_relationship like 'C_ORDER_FROM%' order by location.location

 

Do you know how to define multiple columns in the tables relationship in that lookup properties? 

 

Cheers,

~Juni

Shneor Cheshin
Rank
Userlevel 6
Badge +26

Hi @JuniSihombing

I am not sure having a ‘complex’ join is supported in the baseline configurations.

I can recommend that you create a custom view for that purpose. Have the joins and logic there and use a simple SQL query against that view for the lookup.

Cheers!

JuniSihombing
Rank
Userlevel 4
Badge +11

Thanks for the response @Shneor Cheshin .

 

It seems that’s the only possible way to achieve it currently.  

 

I was trying not to create new configurations (view, sync rule) for it actually, but it seems it must be that way. :-) 

 

Cheers,

~Juni  

 

 

Reply


Terms & ConditionsCookie settings