Help

Merge multiple lookup fields into one lookup field

Topic Labels: Base design
1698 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Doggydatabase
4 - Data Explorer
4 - Data Explorer

I have multiple lookup fields with similar data but from different sources that i would like to have merged into one lookup field. 

Is that possible and if so how?

5 Replies 5

Hi,
I think, you need to clarify. the best way is an example for 2-3 records with data from other sources, that may be similar, but not the same., and what result do you expect from that.

About 'how' - It seems like it cannot be another lookup field. Maybe it should be a formula with text functions.

My database is one table of different dog feed products and in at least two other tables are calculation of the monthly cost depending on different factors of the dog example age and  weight. I would want the prices in the two tables to connect by a lookup field to the product for a nice connection in interface. 

When I now have two lookup fields  with the different prices in the product table I will have to connect and show both fields prices in interface even if one product does not have values in both lookup fields.

If I can't have the merged lookup fields to be a lookup field, is there a possibility to hide an entire section in listform in interface when no values are present for that procuct?

Sorry if my explanation is confusing. Ask if there is anything unclear.

okay, you can create formula field with your fields

LookupField1 & ' - ' & LookupField2

to avoid trailing dash when one or two fields empty, you can use:

LookupField1 &
IF(LookupField1,(IF(LookupField2,' - '))
& LookupField2

second line can be IF(AND(LookupField1,LookupField2), ' - ' ) , doesn't matter
also you can play with MIN, MAX to get range from less to more (or use single value, when they equal).
in interface, show this field instead of two lookups

I tried that but it does not have the desired effect. 

I have added pictures on my interface to explain more what i want. I have different dog feed packages in my database and I want my users to be able to click on the package in the interface to show a record detail view which I have now. In this view I would like the different prices for that specific package to show in a list as shown in the picture below.

Screenshot 2023-09-28 082711.png

But because I have to have different sources for these prices because of different formulas and calculations I end up with sections like below on some packages record detail views. This is because there are no values in those fields.

Screenshot 2023-09-28 082755.png

I would either want to be able to hide the sections that don't contain any values or convert the string values divided by commas (look at the picture below) to a list similar to the first picture in this post.

Screenshot 2023-09-28 083124.png

Is any of these possibilities possible?

I used an automation for this:
Trigger when task is updated - looked at one of the two lookup fields

Action - update the task - update the *other* lookup field. Be sure to include information from the watched field as well as the original field (or it will remove it). Use the ID and not the name of course. Add a comma between the two

Irit_Levi_0-1732705466729.png

In this example, I'm watching the Furniture orders field, and then updating the Orders field with both the information from the Furniture Orders field AND the Orders field. Note the value is the ID.

 

Hope this helps anyone searching for the solution