Using Lookup Fields and "Client Facing Values"

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
elgaucho
Posts: 2
Joined: 2020-07-15 14:37

Using Lookup Fields and "Client Facing Values"

Post by elgaucho » 2024-12-16 07:01

Hi all,

I'm struggling a little bit with an end user usability issue related to lookups. I'm sure it's an easy fix, but it's... irking me!

I know that when you use lookup fields this converts the "database values" into integer values, but (is meant to) keep the cosmetic name in place.

Let me try to explain by focusing on the table relationships:

1. "Companies" Table with id, Company Name

2. "Instruments" Table with id, Company_ID, Company_Name, Instrument_name
Company_ID is a lookup to Companies.id
Company_name is a lookup to Companies.Company_Name, with autofill selected. <- This retains the cosmetic company name

3. "Accounts" Table with id, instrument_ID, Instrument_Name, Company_Name
Instrument_ID is a lookup to Instruments.id
Company_Name is a lookup to Instruments.Company_Name <-- My problem is here. This value shows as an integer and not the Company Name
Instrument_Name is a lookup to Instruments.Instrument_Name <-- loads correctly

How do I make Accounts.Company_Name show the text description and not the lookup integer value?

This is also impacting some SQL lookups as I can't join an integer value to the original table text value, hence why I've added an integer based linking key (e.g. Instruments.company_id) in the tables so my SQL's will work.

Thanks in advance for any help or advice! :)

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1904
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Using Lookup Fields and "Client Facing Values"

Post by jsetzer » 2024-12-16 07:59

There are many ways you can do this. The following two ways have proven to be very practical for me:

You could create an additional field which then will hold the human-readable text instead of the primary key.
  1. Calculated Field: this field can be configured as readonly + calculated field. Then set SQL query accordingly, especially the LEFT JOIN or INNER JOIN from this table to the other source table.
    Example
    AppGini_4mHFGepJyx.png
    AppGini_4mHFGepJyx.png (164.29 KiB) Viewed 1213 times
  2. Another approach: update that field in database for all (or relevant) records in TABLENAME-init()-hook-function.
    Example
    In hooks/partners.php:

    Code: Select all

    function partners_init(&$options, $memberInfo, &$args)
    {
        // you can add LEFT- or INNER JOINS or any SQL update command
        $sql = "UPDATE partners SET label=concat_ws(' ', name, name2)";
        sql($sql, $err);
        // ...
    }
    
As soon as you have that extra field, you can use it in lookup-fields of other tables.

Both have pros and cons. On large tables take runtimes into consideration and perhaps limit the number of records being updated.

I prefer 2nd approach over calculated fields because this gives me maximum flexibility and I can change code on premise without re-generating and deploying the app to the customer's server. Even if customer changes their mind and need extra information, it's just one line of code for me. Additionally, I can be 100% sure all relevant records are up to date which calculated fields cannot guarantee.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 25.10 + all AppGini Helper tools

elgaucho
Posts: 2
Joined: 2020-07-15 14:37

Re: Using Lookup Fields and "Client Facing Values"

Post by elgaucho » 2024-12-16 08:14

Vielen Dank jsetzer!

A great answer, as many of the ones I've read while browsing the forums over the years.

I will have a look, but concur that option 1 would work in the immediate term.

hooks is not something that I have looked into yet, but I will take your advice on board also! :)

Post Reply