In table ‘School’ I want to check two attributes, street and house number and see if they in combination appear in table ‘Address’. I know how to make a lookup which can check if street is in reference table Address, but I want to know if the combination street + house number as a record is in another table as a record.
So below record 1 is valid, but record 2 and 3 both have house numbers in Address table, but not in combination with the street. Is this possible in ONE Web?
School
street
house number
1
West street
45
2
West street
47
3
West street
49
Address
street
house number
1
West street
45
2
East street
47
3
North street
49
Page 1 / 1
Hi @JTH,
You could build the lkp in ONE from some Catalog Item or SQL Catalog Item.
In this case we would need to combine those 2 fields so we can create an sqlCI concatenating those 2 fields from the Address Table:
select street|| '_' || house_number as key
from "orch"."address"
where street in ('MDM','ORCH')
Then create and build a lkp from that certain sqlCI with concatenation of those 2 fields.
I used sample data in my example. Below is the lkp and sample values:
Then you can create a rule in ONE with 2 attributes street and house_number. Also create a variable concatenating the 2 attributes: street+'_'+house_number
You could use a simple logic whether var value is from the lookup: test_kostas_lkp
Test the DQ rule in ONE:
Please let us know if that could work for you.
Thank you
Konstantinos
Hi @konstantinos.patikas,
Haha I was really hoping to avoid SQL CIs, but I'll give it a try! - thank you!
Btw, can't I just do the same what you did, but instead of looking at a SQL CI lookup I will look at the Catalog Item that's already there?
Hi @JTH
Absolutely, I understand wanting to avoid SQL CIs when possible!
Yes, you can reference the existing Catalog Item directly instead of creating an SQL CI. However, in this case since the key of the look up is a concatenation of 2 fields it wouldn’t be possible to do it while creating the look up with the CI. You would have to prepare your data set (sqlCI-concatenate the 2 columns) and then create and build the lookup based on the concatenated key.
