Lookup Fields pull same data, not unique to record

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
bkerr
Posts: 7
Joined: 2020-11-04 18:22

Lookup Fields pull same data, not unique to record

Post by bkerr » 2020-11-16 00:30

I hope I'm explaining this correctly as I'm new at this. I have a customer record and I have a tab for "trips", so I need to be able to see where they have been in the past, so I made the lookup fields to pull the information that I need. I tried to set the unique ID as the field to pull data, but then the other data only copies from the first record.

Screenshot 1 shows multiple trips history, but has the same data, Screenshot 2 shows multiple trips with unique data for customer. Am I coding something wrong?

Thanks
Attachments
screeshot 1.jpg
screeshot 1.jpg (42.65 KiB) Viewed 2542 times
screenshot 2.jpg
screenshot 2.jpg (41.66 KiB) Viewed 2542 times

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

Re: Lookup Fields pull same data, not unique to record

Post by jsetzer » 2020-11-16 00:49

It seems you are referencing from a customer record to two related trip-records using 2x lookup in customers-table + 2x 5 autofills in customers table. This is called a non-normalized* data model which should be avoided for several reasons.

Recommendation

Instead of having n sets of fields in customers table (n x (1 lookup + m autofills)) I recommend defining a normalized* model which has many advantages over non-normalized models and less problems with autofills.

customers
  • id
  • ...more fields, but all customer-related
trips
  • id
  • customer_id
    lookup referencing customers table
  • agent_id
    lookup, referencing agents (or employees) table
  • date
  • destination_id
    lookup, referencing destinations table
  • cost
  • vendor_id
    lookup, referencing vendors table
  • commission
  • ...more fields, all trip-related
You should think it over other way round:
Not the customer has to store two trips, but every trip belongs to exactly one customer.
So, not the customer will hold the id's of two trips, but each trip (child) will hold the id of the related customer (parent).

You will be able to add as many trips as you like to each customer, not only two. This is much more flexible and scalable for future purposes.

* info about normalization:
https://en.wikipedia.org/wiki/Database_normalization
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 24.10 Revision 1579 + all AppGini Helper tools

bkerr
Posts: 7
Joined: 2020-11-04 18:22

Re: Lookup Fields pull same data, not unique to record

Post by bkerr » 2020-11-16 19:36

Thanks, I will try and wrap my head around that and adjust the tables. Makes a lot of sense to be able to scale

jaddison
Veteran Member
Posts: 34
Joined: 2020-06-12 12:44

Re: Lookup Fields pull same data, not unique to record

Post by jaddison » 2021-12-23 08:25

I have much the same problem but mine is a game of football. There is an away side and a home side. Both home and away fields lookup the same Teams table for selection. An auto update field will present the logo for the selected team. Trouble is the auto update field for the second selection presents the logo for the first selected team. Please help

John A

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Lookup Fields pull same data, not unique to record

Post by pbottcher » 2021-12-23 20:25

Hi,

I think this is currently not possible with AppGini.
But what you can do is to create a second table team_copy (copy of the team table). Instead of using the table directly, you can now remove that table via phpmyadmin or similar and create a view to the original team table.

So you will have

team table
team_copy = view to team table

Now you can use the lookup for home to the team table and the aufofil for the logo and use the lookup for the away to the team_copy view.

AppGini does not know about the view and will treat it like a normal table.

An other solution would be to create a team_copy table and use the hook -> TABLENAME_init function to sync the team table to the team_copy table.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

jaddison
Veteran Member
Posts: 34
Joined: 2020-06-12 12:44

Re: Lookup Fields pull same data, not unique to record

Post by jaddison » 2021-12-23 23:29

Hi pböttcher
I know why you have the Superman logo, brilliant. The view method works fantastic.

Thanks again
John A

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Lookup Fields pull same data, not unique to record

Post by pbottcher » 2021-12-24 10:58

Hi John,
thanks for the feedback.
Glad it works and I could help.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

Post Reply