store VALUE instead ID

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
User avatar
naturick
Veteran Member
Posts: 39
Joined: 2014-07-07 18:47
Location: Cluj-Napoca
Contact:

store VALUE instead ID

Post by naturick » 2018-06-03 07:37

Hello,
I have a project similar to invoice system. I have product list with prices.
I have an order table where I can add as many product I want.

I would like to store product description and prices as "text" not as ID of product table.

I need it because some product description and prices may change.

I see that normally the way is to create historical prices for products but I don't like this way.

So I should need to have list of products (look-up field) with auto fill prices...but to store that value as value, not ID.

Do you have any suggestion?

Thanks.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: store VALUE instead ID

Post by pböttcher » 2018-06-03 21:19

Hi,

I would do that via the javasscript possibility which is provided by AppGini (hook/<tablename>-dv.js).
Check the value of the selected product and retrieve the price.

If a price is already set and the product did not change, do nothing, otherwise retrieve the new price of the product.

regards
Pascal
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.

User avatar
baudwalker
Veteran Member
Posts: 188
Joined: 2015-02-03 08:08
Location: Bellingen NSW Australia

Re: store VALUE instead ID

Post by baudwalker » 2018-06-04 04:36

Hi, Have a look at this, see how it goes. I never tried it.

https://forums.appgini.com/phpbb/viewto ... f=6&t=2618

Barry

User avatar
naturick
Veteran Member
Posts: 39
Joined: 2014-07-07 18:47
Location: Cluj-Napoca
Contact:

Re: store VALUE instead ID

Post by naturick » 2018-06-04 12:57

Thanks for replays.

Onestly the javascript solution is far from my knowledge.

This solution https://forums.appgini.com/phpbb/viewto ... f=6&t=2618 should be simple but I don't understand where I must to put it...

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: store VALUE instead ID

Post by pböttcher » 2018-06-04 13:43

I think the link is not really giving you what you need, as it describes the way on how to retrieve the value in the parent table for the id stored in the child 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.

User avatar
naturick
Veteran Member
Posts: 39
Joined: 2014-07-07 18:47
Location: Cluj-Napoca
Contact:

Re: store VALUE instead ID

Post by naturick » 2018-06-05 08:13

So, no easy way to store values....

grimblefritz
AppGini Super Hero
AppGini Super Hero
Posts: 336
Joined: 2015-12-23 16:52

Re: store VALUE instead ID

Post by grimblefritz » 2018-06-05 20:57

Right, if you find javascript difficult, I think there is no easy (through the builder interface) to do what you want.

If you know even a moderate amount of javascript, though, it shouldn't be too challenging.

User avatar
naturick
Veteran Member
Posts: 39
Joined: 2014-07-07 18:47
Location: Cluj-Napoca
Contact:

Re: store VALUE instead ID

Post by naturick » 2018-06-06 04:40

I thought to find here a quick solution, hook, after_insert, after_update..

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: store VALUE instead ID

Post by pböttcher » 2018-06-06 06:46

The question is about what you try to achieve. As you want to have have an autoselect on one side, but do not want the autoselect on the other side.
So how is your logic about when to change the value of the field? If the parent changes?

You can use a regular text field and use the SQL statement provided in the link to add to the hooks and produce your "autoselect" like this on your own.
The drawback is, that is will only be shown in your frontend after you save your record.
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.

User avatar
naturick
Veteran Member
Posts: 39
Joined: 2014-07-07 18:47
Location: Cluj-Napoca
Contact:

Re: store VALUE instead ID

Post by naturick » 2018-06-06 12:58

Thanks for suggestion.

A good example is when a customer changes address....

The address on old documents must remain the old one....the new documents must have new details....

Customer remains the same (same ID)

So the way is that value stored on document is as TEXT not ID. But at the same time should be auto-filled from customer table with actual data.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: store VALUE instead ID

Post by pböttcher » 2018-06-06 18:12

ok, so if you dont mind that when you fill the form the first time the data will not be shown unless saved, you coud do that via the after_insert hook and the before_update hook.

The after_insert hook would query the value for the "autofill" field and store it in the database for the just added row.

The before_update hook would check if the parent field id changed. If so, it would retrieve the new "autofill" value and store it for the updated row. Otherwise no change would happen.

In my app e.g. I need the fact that the autofill will update the information based on the latest available information.
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.

User avatar
naturick
Veteran Member
Posts: 39
Joined: 2014-07-07 18:47
Location: Cluj-Napoca
Contact:

Re: store VALUE instead ID

Post by naturick » 2018-06-07 06:36

That's it....

any help on query?

thanks.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: store VALUE instead ID

Post by pböttcher » 2018-06-07 10:16

Hi,

the query is in the post mentioned by barry

https://forums.appgini.com/phpbb/viewto ... f=6&t=2618

Just adopt it to your tables and fields.
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.

grimblefritz
AppGini Super Hero
AppGini Super Hero
Posts: 336
Joined: 2015-12-23 16:52

Re: store VALUE instead ID

Post by grimblefritz » 2018-06-07 23:59

The better way to handle this, in my experience, is to use an address table. Customer A moves? Add the new address in the address table, and then in the Customer A record select the new item from the address table.

In your transaction records, make sure they contain an address_id field and that it gets populated with the current value from the customer record. You'll have both customer_id and address_id in the transaction record.

This way the historical records will still point to the address in use at the time, while the customer record will point to the current address.

And there's no need to jump through hoops trying to store all the address data with the transaction. It's just a normal AppGini lookup field.

As a bonus, with both IDs in the transaction records it is easy to lookup all the addresses used by a customer, and also all customers that might have used the same address.

User avatar
pilandros
Veteran Member
Posts: 93
Joined: 2014-02-13 18:19

Re: store VALUE instead ID

Post by pilandros » 2018-07-17 20:52

May I suggest to create a field "product_details" (or whatever name works for you) with a type "text" that holds the data or info for each product involved in the transaction at the time of the creation of the document. Then you may populate this field by using the "hooks/your-table.php" script, modifying the "your-table_before_insert" function to create a string of data for the product by reading the $data[] array concatenanting each $data element needed to create a string that will be saved in the "product_details" field. This way you will save the products' details at the time of the transaction.

rprevost
Veteran Member
Posts: 47
Joined: 2018-05-30 22:09

Re: store VALUE instead ID

Post by rprevost » 2018-12-02 22:47

Hello to you all,

I deeply sympathize with people who tried over the years to explain and to convince "seniors-knowledable-appgini-experts" about the importance of Storing value instead of ID in a field.

Three distinct situations are possible :

A: Recuperate the current phone number of a client in the order entry view. This data does not have to be stored in the order table at all. All is needed is a lookup to the phone number linking ClientID in both tables. If one is looking back at an old order entry for a given client, the current phone number must appear on the order entry form (one does not care that the phone number changed 5 times since the original entry was done. No one needs the history of the phone numbers, only the current phone number which the value found in the Client table. So no field is needed in the Order table for phone number.

B: Enter the unit price of a product at the time of the sale (after negociations or whatever reasons) in the detail items for the order. In this case there must be a unit_price field in the detail_item Table, this value must not change after sale. The value must be stored in the table. If one is looking back at an old order entry for a given client, the unit_price of the item is the one decided at the time of the sale whatever the current unit-price of the item in the Product table. No one needs the history of the change in unit_price over time. Now for practical reasons, one would display the lookup value (current lookup in situation A) from the product table just above or near the field used to store the decided on the spot unit_price it would help the sales rep. One could also display the current cost value of the product.

C: This situation is a combination of situation A and B. On the detail_item form there is an editable box (field) where the sales rep can enter the unit_price of the item after negociation. On entry of the productID from the product table, the lookup value of the current unit_price is display inside the editable box (field). If there is no negociation, this current unit_price will be entered in the detail_item table. If there is a negotiation with an agreement on a different unit_price compared to the current unit_price, the sales rep can enter the agreed price in the unit_price field of the detail_item table. This is a true default value that can be edited by the user).

At the moment, the only solution is to use situation A to DISPLAY the current unit_price and add a field just like in situation B.
What we want is implementation of situation C (create a field and put a lookup of the value as the default value).

I started building business database applications (Point of sales and inventory) in 1984. Many clients were using a Macintosh. The only database available was Double Helix (alias Helix Express, alias Helix). It was a 100% graphical interface. No code whatsoever. If one wanted a display lookup, there was a lookup tile for it (you just had to drag field icons in it to make it work). If one wanted an entry field, there was a number tile for it. If one wanted an entry field with a default value, all you had to do is drag the lookup tile over the entry field. So elegant and easy to do. This was a Mac app only and the owner of the product was not marketing savvy at all so today the new owner (group of dedicated people) are struggling to keep it alive. Today a Web application is a must and AppGini is very nice, easy to use and very customable (if you master php and javascript). I love it and if the lookup functionning could be improved (including auto-fill specific to a field and not the first field that is linked to the parent table) I would love it even more. Thank you all.

Post Reply