Fetching data from other table after typing

Applications Forms
, , ,
#1

Hello,

I need to know if the following scenario is possible:

Table1 (Field1, Field2, Field3)-> [Form1]
Table2 (FieldA, FieldB, FieldC)-> [Form2]

Creating/Editing record of Form2

When finish to typing a new record’s FieldC value*, application will automatically check if the typed value matches to a record’s Form1 Field and automatically fetch the data of Field1 & Field2 to current’s open Form2 record FieldA & FieldB.

This has to be done before the record is saved, on the fly. A proccess like example’s of http://www.scriptcase.net/scriptcase-samples/php-filter-search/capture-linking-in-a-filter/ is not an option for now.

*ideally after I click tab on keyboard for going to next field. Alternatively would do the same if there was a button after the FieldC to ignite the process (check value and fetch the data).

#2

First of all there are several ways of doing this. But with pro’s and cons. One approach might be: when you type a value in a field and you want that this field can be found in some table you could use a dropdownbox or an ajax text search (text autocomplete). Also you can create a caption link to retrieve the value from the other table. To retrieve additional info you can set an ajaxevent onchange to select or check data. If this needs to be done when you leave a field you need the onblur event. I always do a php check on my data in the onvalidate event because the ajaxevent sometimes don’t trigger, i.e. you have an onblur and then the user directly clicks on a button.

#3

Thank your for your prompt reply.

The first solution does not fit. Drop Down box will be useless or ajax text search will be extremely slow since there will be thousands of these kind f values that have to be searched.

The second solution might fit but let me explain why I need this:

[LIST=1]

  • There will be an application form (Form1) that there will be inserted and save client details (thousands of clients): First Name, Last Name, VAT number so they can be used in future. [/LIST]

    [LIST=1]

  • There will be another application form (Form2), for example contract details, which among others will have: First Name, Last Name, VAT number etc. [/LIST]

    Now, since for productivity reasons, we want in a new contract form (Form2), First Name & Last Name to be filled automatically, we will use the VAT number field to make a lookup in Clients’ records(Form1), and if it finds a Client record’s VAT match to fetch its First Name & Last Name details. VAT number will be used as a key since it is the only thing that a legal or individual person has always the same.

    Do you believe suggestion 2 will fit ?

    • #4

    I would go for a caption link. The called application could be a grid default starting with a search option. For a nice appearance this caption link could be a modal form which pops up.

    #5

    Searching through modal box slows down the data entry productivity since it is 2 or 4 mouse clicks more.

    Is it impossible to have just a button beside the FieldC which triggers the search/find match/fetching process without opening anything else? In a very similar database builder that we were using years, had this function ready to be used and was very helpful when working on a large amount of data entries in daily basis.

    #6

    After almost 10 hours of trying to make it, it seems that it was pretty easy.

    I had to make an Ajax Event (onBlur) on Form2:

    
sc_lookup(dataset, "select fullname,address from clients where vat='{vat}'" );
{fullname} = {dataset[0][0]};
{address} = {dataset[0][1]};

    Now when I am on a new contract’s record (Form2), I enter the VAT number and when I press Tab button, his Full Name & his Address are being auto filled in the next two fields by being fetched from another table (Form1) which contains clients’ records (VAT,Full Name, Address).

    Now, for some reason I feel that this way was quick & dirty and not the orthodox way that it might had to be. Can you suggest me if I’m wrong in something?

    #7

    Why? You are capturing a client side event, and doing server side a query. Remember Web applications are synchronous, it’s not like desktop development. You have Server Side, and Client side.

    #8

    What if I need to make a button to trigger this event manually instead using onBlur ?

    #9

    The disadvantage of this onblur is that you need to enter the full vat to be able to select, and what do you do if this field is required or the number does not excists? You can consider doing a like so you can search for a part of the key, but as VAT numbers can be long I still think that a capture link would be best. But if it works for you who am I to deny :wink:

    #10

    For our needs , not to display the VAT while you type it is an advantage. Like I said there are thousand of client records to be migrated and it will make it extremely annoying and slow to start showing numbers as you type, even the typing will slow since the human eyes has to check if they are close to a match. Imagine that the people that do the data entry, they already know the VAT number from the contract paper copy that they are holding at that moment. That’s why in the current web based application that built with another database builder software, we had it like this.

    As for what if there is not a VAT number match, there is no problem, nothing happens to next fields. There will be just an information box that will show that this is a new client and if you wish to make a new record for him in Clients records (Form1) directly from the contract record’s fields (Fullname,Address,VAT) (Form2)

    :slight_smile:

    #11

    Still need your lights on it…

    #12

    You can make a button and do a sc_exit(sel) to return. But this requires a lot of programming as it will call your application from scratch and you need to have saved the necessary stuff in a global variable and use that in the onscriptinit/onload to view. I’ve done a feature request a long time ago to SC to trigger an ajaxevent by an onclick of a custom button but no luck so far.

    #13

    Albert, this guy is working for VAT people, check if he is in NL then this application may charge you later at some point loooooool just kidding ,wanted to say hello :slight_smile: