Page 1 of 1

Left() in lookup

Posted: 2017-12-14 06:50
by baudwalker
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

Re: Left() in lookup

Posted: 2017-12-18 14:44
by R Tammam
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)

Re: Left() in lookup

Posted: 2017-12-18 23:40
by baudwalker
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

Re: Left() in lookup

Posted: 2017-12-24 10:29
by R Tammam
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

Re: Left() in lookup

Posted: 2018-10-18 05:17
by baudwalker
I still have not got this to work. I would like some assistance with what code goes where.
Thank you

Re: Left() in lookup

Posted: 2018-10-18 20:19
by pbottcher
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.

Re: Left() in lookup

Posted: 2018-10-19 08:15
by baudwalker
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.

Re: Left() in lookup

Posted: 2018-10-19 18:49
by pbottcher
Hi,

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){
		  	    }
		});
	});
});
In the base directory you create a file ajax_##TABLE1##_fill.php

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;
	}
?>
Change the Town field to a normal text field.

Hope it works

Re: Left() in lookup

Posted: 2018-10-20 05:03
by baudwalker
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