Ask Your Question
0

Base Lookup

asked 2020-02-18 02:43:36 +0100

bbater gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-02-18 03:39:09 +0100

Ratslinger gravatar image

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.

edit flag offensive delete link more

Comments

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 gravatar imagebbater ( 2020-02-18 17:25:11 +0100 )edit

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
bbater gravatar imagebbater ( 2020-02-19 01:39:30 +0100 )edit

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.

Ratslinger gravatar imageRatslinger ( 2020-02-19 02:32:26 +0100 )edit

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... :-)

bbater gravatar imagebbater ( 2020-02-20 00:23:35 +0100 )edit

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?

Ratslinger gravatar imageRatslinger ( 2020-02-20 00:55:15 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-02-18 02:43:36 +0100

Seen: 84 times

Last updated: Feb 18