lookup auto-fill

Wish to see a specific feature/change in future releases? Feel free to post it here, and if it gets enough "likes", we'd definitely include it in future releases!
Post Reply
grimblefritz
AppGini Super Hero
AppGini Super Hero
Posts: 336
Joined: 2015-12-23 16:52

lookup auto-fill

Post by grimblefritz » 2016-07-24 15:14

It would be very helpful if there was an option on auto-fill lookups, to store the looked up value in the record, rather than the fkid.

I have tables where it is required (for legal reasons) to record looked up data at the time of transaction. Having the fkid in looked up fields does not meet this requirement, so I end up having to write my own lookup code.

Having the option to record fkid or data, as a native AG capability, would be a great help.

DevGiu
AppGini Super Hero
AppGini Super Hero
Posts: 151
Joined: 2016-05-27 09:08

Re: lookup auto-fill

Post by DevGiu » 2016-07-24 16:20

Warning, Newbie here .

Is not Advanced on Lookups what you are looking for? Just change first id for whathever you want to save.
Attachments
Selección_017.png
Selección_017.png (90.1 KiB) Viewed 8891 times
/Giuseppe
Professional Outsourcing Services

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

Re: lookup auto-fill

Post by grimblefritz » 2016-07-24 16:40

Not quite.

Imagine a PRODUCTS table:

id
name
cost
price

Now imagine an ORDER_ITEMS table:

id
product_id
name
cost
price
quantity
subtotal

You can setup a lookup, as you illustrate, so when entering ORDER_ITEMS you can select PRODUCTS from a lookup. Yes, you can use advance to fiddle with the sql, but that is only modifying the product_id field handling.

Now imagine, you also want to auto-fill the name, cost and price fields in ORDER_ITEMS, using the values from the PRODUCTS table, as selected by the product_id lookup.

To do this, you make the cost field, for example, a lookup *also* from the PRODUCTS table. When you do that, you will see that the options for the lookup change. The advanced button disappears, for example, as does the link to parent record option. YOU GET A NEW OPTION, however, Auto-fill. Select the field from PRODUCTS to use, and check Auto-fill. Do the same for name and price.

On the generated page, when you change the product_id selection, these auto-fill fields are visually updated. Very nice when you want to lookup an item and display the detail in the forms.

Behind the scenes, however, what is actually stored in the auto-fill fields is product_id. So if you select product_id 15, name/cost/price will also have 15.

What I need is for name/cost/price to save the looked up value, not the fkid product_id. I need to record the actual values looked up, not the index into the PRODUCTS table.

DevGiu
AppGini Super Hero
AppGini Super Hero
Posts: 151
Joined: 2016-05-27 09:08

Re: lookup auto-fill

Post by DevGiu » 2016-07-24 16:53

Oh ok, auto-fill "others fields".

Yes, is interesting this, because maybe you want to auto-fill, and at the same time, change for example the price just for this order.
/Giuseppe
Professional Outsourcing Services

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

Re: lookup auto-fill

Post by grimblefritz » 2016-07-24 21:27

Yes, but in this case, it's a dynamic PRODUCTS table and a legal requirement to be able to document product data as it was at the time of the transaction.

A simple lookup that dynamically pulls data won't work, since the data from the lookup will likely have changed. Today's $1.17 unit cost may be $1.24 next week. Or $1.12.

Rather than have a system where PRODUCTS change transactions are logged, and then have to do a time-filtered lookup from the ORDER_ITEM into the transaction log in order to obtain data at the time the transaction transpired -- it's simpler to just write the PRODUCTS data in with the order transaction detail.

I have a workaround for this, already in use, but it would be cleaner and simpler if it were a native AppGini mode.

Strider27
Posts: 8
Joined: 2016-08-21 22:49

Re: lookup auto-fill

Post by Strider27 » 2016-08-30 19:19

Could you please share your solution, as I just realised as I read the post, I will encounter this issue in the next couple of days of development.

I do agree this should be supported by the application as this will render majority of solutions that have any type of invoicing/quotation system, unreliable, product prices fluctuate all the time and auditing would be a nightmare.

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

Re: lookup auto-fill

Post by grimblefritz » 2016-08-30 21:02

I'm using a bit of jquery in the tablename-dv.js hook. Basically:

I define the additional fields in the designer, so they exist in the table.

I then use jquery to make them uneditable.

Next, I use a jquery onChange event to watch for the lookup field to change.

I then get the new lookup key value via jquery, and use it to make an ajax call to fetch the complete record from the lookup table.

The data returned via ajax call is then used to populate the additional "uneditable" fields.

Does that help? I can post a code example if you need it, just not right at the moment.

Strider27
Posts: 8
Joined: 2016-08-21 22:49

Re: lookup auto-fill

Post by Strider27 » 2016-08-30 21:27

I have found my answer in the Udemy tutorial, you can populate the field with the value from the parent record, on change, using ajax in a separate file (I would post the code but not sure how Ahmad feels about reposting code from the tutorial). If you have access to the tutorial it is in Section 10 video 26.

Please do post your code when you get a chance, it would be interesting to see how you solved it, and it may be more relevant to some cases than the method in the tutorial.

Strider27
Posts: 8
Joined: 2016-08-21 22:49

Re: lookup auto-fill

Post by Strider27 » 2016-08-30 21:47

I just realised that's pretty much what you said. My apologies.

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

Re: lookup auto-fill

Post by grimblefritz » 2016-08-30 23:04

LOL

I took my queue from the Udemy course, so there's nothing earthshattering in how I did it.

To anyone looking for a solution, my best advice is TAKE THE UDEMY COURSE!

You might want to search out my posts regarding "disabled" fields. I didn't like how AppGini readonly works, and setting readonly via jquery allows the cursor to enter the field, and setting disabled via jquery causes field contents to not only not be updated but overwritten in the database with a null value!

So I devised a way to get the best of readonly and disabled, without the quirks of either.

Strider27
Posts: 8
Joined: 2016-08-21 22:49

Re: lookup auto-fill

Post by Strider27 » 2016-08-31 13:58

I second that, the Udemy course is really useful for customising they way your app works.

@grimblefritz I saw the posts on the read-only issues and although it seems to be working for me as intended (both jquery disable and setting read-only in appgini) I have not done extensive testing yet, I'm sure I will encounter this Issue later. Thanks for all your contributions on the forum, it does help out a lot.

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

Re: lookup auto-fill

Post by grimblefritz » 2016-08-31 14:45

Bear in mind that jquery disable will result in a null value being written to the database when you save. That may be ok in some cases, but in my case it wasn't acceptable.

Glad to help where I can. I like the product, I want it to continue to improve, and a lively forum is one of the best ways to grow the community and foster development.

Post Reply