This topic is locked
[SOLVED]

Autofill help

7/30/2021 6:23:30 PM
PHPRunner General questions
L
leteck author

I have a lookup field (Apartments) where I want to autofill Bedrooms but Master and Child have constraints and It doesnt work. My question is : How can I get the value of Bedrooms based on Apartments and show in the form after I select the apartment.

admin 7/30/2021

I guess you are looking for dependent dropdown boxes option.

L
leteck author 8/1/2021

No, Because the problem is the autofill. If I dont have constrainst in database autofill works perfect.

HJB 8/1/2021

"... biting into the teacher's hand ..." is not the right strategy to solve the problem. I'm, fully streamlining
with admin's as local test revealed, YES, exactly the proper configuration of the red framed box as per
URL pointed to, is doing the trick to get master/detail related autofill to work as wished.

L
leteck author 8/3/2021

Still not undestand! Sorry

A
acpan 8/4/2021

I have a lookup field (Apartments) where I want to autofill Bedrooms but Master and Child have constraints and It doesnt work. My question is : How can I get the value of Bedrooms based on Apartments and show in the form after I select the apartment.

Database Contraints take effect by DB Server only when data is saved, refer to W3School (Extract from the link: Constraints are used to limit the type of data that can go into a table).

You want to present drop down and filter the drop down based on the other. All the actions are on a form that is not yet saved, i doubt DB contraints can impose any effect at that point as user has not hit the submit button to let DB server enforces the constraints you set in DB Server.

And your desciption seems a common scenario for Dependant Drop Down or AutoFill function. There are many tips in the forum and manual.

You may want to provide details, what are the constraints you set, and how you test before and after you remove constraints, which you think that causes auto-fill to work and not work.

L
leteck author 8/4/2021

THIS IS THE SCENARIO:

Creating an Invoice:

  1. I select the Customer from a Drop down (database)


  2. Select Building from dropdown (Database)


  3. Select apartment from drowpdown filtered by Customer and Building (One Building could have different customers)

    When I select the apartment I want to autofill the bedrooms field on Invoice with Beds on apartment. Both fields are same datatype but autofill is not working



If I try it withow indexes and foreing keys for INVOICE table and INVOICEDETAILS , autofill work but then I lose contraints.

L
leteck author 8/4/2021

INVOICE
INDEXES fk_CustomerID_IDX CLIENTE_IDCLIENTE NORMAL BTREE
FOREING KEY fk_CustomerID CLIENTE_IDCLIENTE MAIDDATA CUSTOMERS SYSID

INVOICEDETAILS
INDEXEX fk_details_invoice_idx factura_idfactura NORMAL BTREE
FOREING KEY fk_details_invoice factura_idfactura MAIDDATA INVOICE IDFACTURA CASCADE CASCADE

admin 8/4/2021

As @acpan mentioned, constraints are only relevant when you modify the data in the database. Autofill only retrieves the data so it is not possible that constraints would affect this functionality. There must be something else involved.

A
acpan 8/4/2021

You said: If I try it without indexes and foreign keys for INVOICE table and INVOICEDETAILS , autofill work but then I lose contraints.

Again, AutoFill has got nothing to do with the DB constraints. When you can select the drop down and populate the fields, AutoFill's job is done. It is the DB updating part (when Submit button is clicked), and you need to see what actual statement goes into the DB that triggers that.

I suggest your print out the SQL Statement at Before Record Updated/Added event, and run it using PHPMyAdmin and examine what are the constrains voilation against your rules in DB.

Based on your description of your tables, you have several foriegn keys that link to the Invoice Table. There could be some fileds that are not supposed to be empty due to being foreign keys and they are missing from your SQL statement, likely you did not include them in your form (you can include those fields and hide them on the form).

Then modify your SQL at Before Record Added event, to include the default values for the missing fields. eg:
$values["factura_idfactura"] = "default";

C
CecilP 8/10/2021

What if the datatype for the autosave isnt compatible with the source data?

eg: filling a 200 character text into a tinyint field. I had that happen once and with my poor memory never forgot it because it took me 2 days to work out.

I love this feature because with a few helper tables I can do levels and levels deep in the background and it saves hours of work.