Merge multiple lookup fields into one lookup field

[Doggydatabase]

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?

[Alexey_Gusev]

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.

[Doggydatabase]

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.

[Alexey_Gusev]

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

[Doggydatabase]

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.

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.

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.

Is any of these possibilities possible?

[Irit_Levi]

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

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

[Alexey_Gusev]

Hi,

A short notice. In your screenshot, Orders is Linked field. Lookup fields are other type, not editable. Each lookup field based on linked field in the same table and represent the same number of values, as the number of links in linked field. 

I think, the topic question is a bit different. For example, when you have comma-separated value, and want it to turn to a column of values, you can create new linked field (to a new table, maybe even temporary) and paste value to a cell. Airtable gets it as a list of values to link and will create primary column in a new table according to the list. 

About how to convert such list:
Copy to cell. Create formula

SUBSTITUTE(Value,',','\n')

 To get something like this

Now copy value from formula field. Airtable gets it as a single value. but if you paste in notepad, it's quoted - to use as a single value

Remove quotes, ensure to set line feeds between lists:

must be

Now copy all and paste to next empty field. Without quotes, Airtable paste it as a list of values