How do i create a lookup?

(1) Share

Report

Posted on by CU30040608-0

Hi Experts,

I am having some trouble wraping my head around creating lookups in FO. I have looked at a lot of similar questions in here but none helped me, so i hope someone in here are able to.

My scenario:
in Organization administration, on Lega entities you can add and edit an adress. In the form where you create/edit the address i want to add a new lookup field in which the person can choose between diferent office location identifiers. because there might be a lot of office locations, a normal dropdwon wont work. The user have to be able to search it.

I can see that the form is called LogisticsPostalAddress and that some of the fields on the form uses a datasource called LogisticsLocation.

What i have tried:
First of all i have created an extension of LogisticsPostalAddress and LogisticsLocation.

First attempt:
I have then created a base enum and populated it wiht a few office location values.
I have then created a field of type enum on the extended LogisticLocation and used the enum as the enum type property.
I have then added the field to the extended form.
Result: The result of this was just a normal dropdown that you can not search in. So not a lookup.

Second attempt:
I created a new string EDT called OfficeLocationId and set its reference table to LogisticsLocation
Then i dragged the EDT to the LogisticsLocation table
Then i added the field to the LogisticsPostalAddress form.
Result: The result is just a normal string field. No lookup.

My questions:
1. Can anyone tell me what i am doing wrong and guide me through the process using my specific scenario?
2. It is my understanding that the lookup functionality normally is used to reference another table than the table you are already using. How would you go about solving my scenario? Should i create another table to hold the office locations?

I have the same question (0)

All responses (6)

Answers (3)

Verified answer

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

Like (1)

Report

Hi Community User,

For your second attempt, you need to create a relation in order for the standard lookup to appear.

So if let's say you want this "OfficeLocationId" to show all values in LogisticsLocation.LocationId -- then make a relation on LogisticsLocation table between these two fields in order for the lookup to appear.
I mean logisticsLocation.LocationId == LogisticsLocation.OfficeLocationId -- if you give this a try, you'll start seeing the lookup

But if let's say this officeLocationId should have specific values from LogisticsLocation.LocationId, then how do you define these officeLocations? would it for example work if you override the lookup method for this field, to maybe add some ranges on what values to return from LogsiticsLocation.LocationId? or would it require a new table with new LocationIds but maybe linking this new table (table.Location with LogisticsLocation.RecId)? maybe explain to us more what is the business. But in general I think now you know how to make a lookup appear so let me know if this helped please.

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
CU30040608-0 on at

Like (1)

Report

Hi Layan,

Thank you for the answer. It helped me understand lookups even though i could not get it working. I did however think about your follow up questions and desided create a new table to hold all information regarding office locations which worked.
So it seems to be a lot easier getting it working with a related table, then trying to create a lookup to a field on the table i am already using.

Here is what i did:
1. I created a table called OfficeLocation with a Name column.
2. I then craeted two EDT's.
- One called OfficeLocationRecId (representing the id)
- One called OfficeLocationName (representing the name of the location)
I set the reference table property one these two EDT's to the OfficeLocation table.
3. I then went to the LogisticLocation table and creted the two fields here setting their exteded data type property to the matching EDT's.
4. I the created two new relasions on the Logisticslocation Table
- LogisticsLocations.OfficeLocationRecId == OfficeLocation.RecId
- LogisticsLocations.OfficeLocationName == OfficeLocation.Name
5. finally i added the the name field to the form
Result: I got my lookup :)

Follow up Questions:
1. What do you think of my approch?
2. Taking into considuration the following business case, whas it the right choise to create a new table holding the office locations?:

The Business want to be able to add a Office location identifier (some kind of short name for the office location) to office addresses when adding these to lega enitties, so that they can easely and seperate office locations from each other.

Was this reply helpful? Yes No
Verified answer

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

Like (1)

Report

Hi Community User,

I still don't understand what does this "officeLocationId" mean from a business point of view when you add it to a legal entity address. should this officeLocationId be a lookup of existing LocationIds in the logistcsLocation Table? if yes then no need to create a new table.

I'm not sure why lookup didn't work for you. Did it not work after you added the relation? because I just did a quick example and it worked.
I created this EDT(ignore the dummy name)

I added the EDT to logistics location table and added a relation

Then i added it to the form, and i can see values in the lookup if i click on it

if this is not your desire, and you would like for example some LogisticsLocationIds to appear from LogisticsLocation based on some criteria. Like the ones who has isPostalAddress as false, then we can override the lookup field method.

If I'm going to choose the table approach, then I'm assuming you would like those officeLocations to have specific data. So for example, maybe you want to create a form for this table, where you would like to create addresses for those offices locations. So when the user opens the form, he can create a new officeLocation, add a name to it and add an address. And maybe more setup fields can be added based on the business need, like openning timing.
Also another reason to create a new table, is if i will need different tables to use this officeLocationId

So i would say this table should contain the following fields:
OfficeLocationId -- new string edt it doesn't need to extend anything. it's "reference table" should be the new officeLocation table

LocationName -- string field

Location -- int64 (extends LogisticsLocationRecId) -- add this If you would like the officeLocations to have their own addresses. And in this case you will need to add relation on the officeLocationTable:
officeLocationTable.Location == LogisticsLocation.RecId

Then u can add this "OfficeLocationId" to LogisticsLocation and add this relation on LogisticsLocation table: LogisticsLocation.OfficeLocationId == officeLocationTable.OfficeLocationId
(I'm not saying this is a good solution as I don't understand the business but i'm trying to acheive your goal from a technical point of view)

However, please note when adding this officeLocationId to logisticsPostalAddress form, that this would mean that this field will appear in all postal address forms, like customer address, vendor address etc.. So if you wouldn't like it to appear everywhere, then you will need to handle this by code.

Please verify the answers that were helpful

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

1 people found this reply helpful.

Was this reply helpful? Yes No
CU30040608-0 on at

Like (1)

Report

Hi Layan,

Thank you for the detailed explanation. It worked! So, I must have done something wrong before. I am really learning a lot here 😊
Regarding you questions about the business case. I will try to explain it better:

So let’s say we have a big company with offices in multiple countries and offices in multiple cities in each county. Maybe even multiple offices in the same city. One of these countries could be Germany. Let’s say that we in Germany have 10 offices spread across different cities. Let’s say that we in Berlin have 3 offices.

The Human resources team would now like an easy standardized way to identify these office locations when they add addresses to legal entities. The identifiers could look something like this (just some random example):

DeBe01
DeBe02
DeBe03

De (for Germany)
Be (For berlin)
01 (for office 01)

I don’t think I can use the already existing LogisticLocation.LocationId for this. So I thought that one of the following solutions might work.

Two possible solutions:
1. I can create a new column in the database that simply holds some identifiers that Human resources can pick from when creating an address.
2. I can create a new table that holds the identifier in case we want to add more columns in the future (like description).

How would you solve this case? 😊

Was this reply helpful? Yes No
Verified answer

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

Like (1)

Report

Hi Community User,

I think the original question is solved then which is how to create a lookup and you already said that the answers helped. So can you please mark the two answers that helped you as verified.

Regarding your case scenario, maybe it's better to create a new question as the original question is answered.

However, you said you have one big company, so in the legal entity I'm assuming you should add the address for the company. But are you saying that currently in the legal entity address grid, you are inserting three addresses for the three offices in berlin? and you want to fill this new field for each address like this ?
DeBe01
DeBe02
DeBe03

if that's the case, can't you utilize the locationName for example?

And is it easy for human resources to identify DeBe01 or 02?

Now going to your two possible solutions:

In solution 1 , do you mean an enum? or string field? because if it's an enum, it will mean that each time a new office is introduced you will need to do a new development. if it's a string, then it could work, but i still doesn't understand why human resources would want it.

For the 2nd solution, i already told you before, if you think u will need to add extra fields regarding these locations, then yes a table is good.

I do need to understand if you are inserting the addresses for all offices inside legal entity address grid and the importance of this field in order to help.

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
CU30040608-0 on at

Like (1)

Report

Hi Lyan,

Thank you so much for your help. I agree that the question has been answered. I dont know why but when i try to mark your answers the site just keeps loading forever until it chrashes. I will keep trying to mark your answers the next days.

For now i will reference the answers here. The following answers helped me sovle my problem:

The post from the 13 Nov 2023 at 20:11:59
and
The post from the 14 Nov 2023 at 22:40:53

Was this reply helpful? Yes No