Base Lookup

[bbater]

System: Windows 10, LO 6.3.4.2 (x64), Firebird

Me again! I want to implement a ‘Lookup’ function in a Base table. When a ‘Nationality’ value is entered in a record, I want an automatic lookup to occur and populate a field ‘Region’ with a value obtained from elsewhere. E.g. if the ‘Nationality’ entered is ‘Ethiopian’, then the Lookup should return ‘Africa’. If the ‘Nationality’ entered is ‘Iraqi’, then the Lookup should return ‘Middle East’. If ‘Pakistani’, then ‘Asia’ etc…

I have searched on ‘Lookup’ in the LO Base context, but find that the results seem to refer to the use of list boxes, which is not what I regard as a ‘Lookup’. Any advice on implementing this in LO Base would be greatly appreciated.

[Ratslinger]

Hello,

a value obtained from elsewhere

The thought here is the value comes from another table that contains the pairing for the value to be applied - Nationality & Region. So that being the case, why duplicate information? This is how NOT to use a relational database. When you need this information, it is just a matter of retrieving it via SQL. This way you always get the correct and current information.

With your request, duplicating this value in another field, you need to write code specific to your forms and table(s). This is also prone to error. Someone can go into a table view and modify the Nationality there. There is no capability to get an updated value even using a macro. Now your information is incorrect. Or it is discovered the inserted value was incorrect. Now the table and all the records containing that value need to be changed.

Best method is to retrieve the data when needed via SQL.

[bbater]

Hi Ratslinger. So, you’re saying that rather than create a second table containing ‘Region’ which is ‘looked up’ via Nationality_ID as a FK, simply include ‘Region’ as a further field in the Nationality table? Yes, of course that makes sense. I think I was just trying to avoid entering ‘Region’ multiple times, but there aren’t that many Nationalities anyway, so what you suggest makes sense. Many thanks.

[bbater]

I thought it simple to amend a former query to COUNT column ‘Region’ instead of column ‘Nationality’. Not so.

I have added a column ‘Region’ to my Nationality table, so the columns are ‘Nationality_ID’, ‘Nationality’ and ‘Region’.

In order to COUNT ‘Region’ instead of ‘Nationality’, I thought it a simple matter of substitutiing ‘Region’ for ‘Nationality’ thus:

SELECT
    c."Nationality_ID",
    n."Region",
    COUNT(*)
FROM
    "Client" c
INNER JOIN
    "Nationality" n ON n."Nationality_ID" = c."Nationality_ID"
GROUP BY
    c."Nationality_ID", "Region";

But the results are not grouping by Region, so I am missing something. Advice would be appreciated.

**Nationality_ID** **COUNT** **Region**
2                           1       Europe
4                           1       Africa
5                           2       Africa
11                         1       Africa
13                         1       Africa
14                         1       Africa

[Ratslinger]

The problem is the inclusion of Nationality_ID in the Select and Group By. By including, it is part of the grouping and unlike the count for Nationality it has no relevance in this statement. Remove those two and I would also change the INNER JOIN to a LEFT JOIN (as noted in a previous comment on another question), but that is up to you.

[bbater]

Ratslinger, my gateful thanks, yet again. Yes, your solution works. I’m now going to have to sit and stare at the revised query and get to understand why…

[Ratslinger]

It’s fairly easy. There is no actual relation between the ID and Region. The relation with ID is Nationality. Region can be in many different ID’s but not Nationality.

This is a reason in a past post I mentioned your query did not need ID returned. Nationality is the same as ID in this instance.

Make sense?