Hi All,
A simple form of what is required follows...
A table with an ID field containing a 4 digit postcode, a second field with the corresponding town.
The second table has an ID field containing a customer number of 6+ digits. The first 4 digits of this number is the postcode and the remaining numbers form a unique customer number. A second table called location
What is required is that when the customer number is entered, the first 4 digits is taken and used to look up the first table and prefill the location field in the second table with the corresponding town.
example: [ WHERE left(second_table.id,4) = first_table.id ] or something sinalar
Problem is how to stick it in AppGini?
Any assistance Please
Barry
Left() in lookup
- baudwalker
- Veteran Member
- Posts: 188
- Joined: 2015-02-03 08:08
- Location: Bellingen NSW Australia
Re: Left() in lookup
Dear baudwalker ,
i think you can make the id in the second table editable so you can edit it in hooks,
and to be able to insert the i as you want , you can in before insert hook , get the first four digits you want to insert , then find the count of records in the table you will insert the record in and add 1 to it , if it's count =1 add 1 and the inserted values will be
(the first four digits you get+the count plus one)
i think you can make the id in the second table editable so you can edit it in hooks,
and to be able to insert the i as you want , you can in before insert hook , get the first four digits you want to insert , then find the count of records in the table you will insert the record in and add 1 to it , if it's count =1 add 1 and the inserted values will be
(the first four digits you get+the count plus one)
- baudwalker
- Veteran Member
- Posts: 188
- Joined: 2015-02-03 08:08
- Location: Bellingen NSW Australia
Re: Left() in lookup
Thank you for you reply but I believe that my explanation may have mislead you. I have attached a drawing of what I am attempting.
The user inputs the (23251324) as the customer number (id).
The first 4 digits (2325) are taken and checked for a corresponding number in Table 2 (id)
then the matching town (Aderdare) is Auto-filled into table 1 (Town)
I hope this explains my thoughts
Barry
The user inputs the (23251324) as the customer number (id).
The first 4 digits (2325) are taken and checked for a corresponding number in Table 2 (id)
then the matching town (Aderdare) is Auto-filled into table 1 (Town)
I hope this explains my thoughts
Barry
- Attachments
-
- lookup.PNG (13.16 KiB) Viewed 4856 times
Re: Left() in lookup
Hello baudwalker,
i got what you need to do , so i suggest you to
add an onkeyup jquery handler on the field to it will check if the field has 4 digits and the size of town field is zero so it sends an ajax request to get the town that has this is (the 4 inserted digits) and write it using jquery in the town field
i hope that it will help you
Regards
i got what you need to do , so i suggest you to
add an onkeyup jquery handler on the field to it will check if the field has 4 digits and the size of town field is zero so it sends an ajax request to get the town that has this is (the 4 inserted digits) and write it using jquery in the town field
i hope that it will help you
Regards
- baudwalker
- Veteran Member
- Posts: 188
- Joined: 2015-02-03 08:08
- Location: Bellingen NSW Australia
Re: Left() in lookup
I still have not got this to work. I would like some assistance with what code goes where.
Thank you
Thank you
Re: Left() in lookup
Hi,
can you please explain the type of fields you are using. Is the Town e.g. a read-only field in AppGini, or shall the user be able to overwrite the "auto-lookup value from the ID.
can you please explain the type of fields you are using. Is the Town e.g. a read-only field in AppGini, or shall the user be able to overwrite the "auto-lookup value from the ID.
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.
- baudwalker
- Veteran Member
- Posts: 188
- Joined: 2015-02-03 08:08
- Location: Bellingen NSW Australia
Re: Left() in lookup
Hi,
Thank you for your prompt reply.
At the moment the Town field is a lookup but most users select the incorrect town, so it would be better to have the town as read only. as the code only fits one town.
Thank you for your prompt reply.
At the moment the Town field is a lookup but most users select the incorrect town, so it would be better to have the town as read only. as the code only fits one town.
Re: Left() in lookup
Hi,
you can try this:
(replace ##TABLE1##, ##TABLE2## with your tablenames)
create in the hooks folder the file ##TABLE1##-dv.js and put
In the base directory you create a file ajax_##TABLE1##_fill.php
Change the Town field to a normal text field.
Hope it works
you can try this:
(replace ##TABLE1##, ##TABLE2## with your tablenames)
create in the hooks folder the file ##TABLE1##-dv.js and put
Code: Select all
$j(function () {
$j("#Town").prop('disabled', true);
$j("#ID").on('change' ,function() {
val=$j("#ID").val().substr(0,4);
$j.ajax({
url: 'ajax_##TABLE1##_fill.php?&code='+val,
success: function(res){
}
});
});
});
Code: Select all
<?php
$currDir = dirname(__FILE__);
include("$currDir/defaultLang.php");
include("$currDir/language.php");
include("$currDir/lib.php");
handle_maintenance();
header('Content-type: text/javascript; charset=' . datalist_db_encoding);
$table_perms = getTablePermissions('test1');
if(!$table_perms[0]){ die('// Access denied!'); }
$val = makeSafe($_GET['val']);
if(!$val){
?>
$j('#Town').val('');
<?php
break;
}
$res = sqlvalue("SELECT `##TABLE2##`.`town` as 'town' FROM `##TABLE2##` WHERE `##TABLE2##`.`id`='{$val}' limit 1");
$row = db_fetch_assoc($res);
?>
$j('#Town').val('<?php echo addslashes(str_replace(array("\r", "\n"), '', nl2br($row['town']))); ?>');
<?php
break;
}
?>
Hope it works
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.
- baudwalker
- Veteran Member
- Posts: 188
- Joined: 2015-02-03 08:08
- Location: Bellingen NSW Australia
Re: Left() in lookup
Great thank you.
Unfortunately I will not be able to try your suggestion for a few weeks as I am heading for an unscheduled eye operation at the moment. I will let you know as soon as I try your code
Unfortunately I will not be able to try your suggestion for a few weeks as I am heading for an unscheduled eye operation at the moment. I will let you know as soon as I try your code