Left() in lookup

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
User avatar
baudwalker
Veteran Member
Posts: 188
Joined: 2015-02-03 08:08
Location: Bellingen NSW Australia

Left() in lookup

Post by baudwalker » 2017-12-14 06:50

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

R Tammam
Veteran Member
Posts: 113
Joined: 2017-08-26 15:35

Re: Left() in lookup

Post by R Tammam » 2017-12-18 14:44

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)

User avatar
baudwalker
Veteran Member
Posts: 188
Joined: 2015-02-03 08:08
Location: Bellingen NSW Australia

Re: Left() in lookup

Post by baudwalker » 2017-12-18 23:40

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
Attachments
lookup.PNG
lookup.PNG (13.16 KiB) Viewed 4702 times

R Tammam
Veteran Member
Posts: 113
Joined: 2017-08-26 15:35

Re: Left() in lookup

Post by R Tammam » 2017-12-24 10:29

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

User avatar
baudwalker
Veteran Member
Posts: 188
Joined: 2015-02-03 08:08
Location: Bellingen NSW Australia

Re: Left() in lookup

Post by baudwalker » 2018-10-18 05:17

I still have not got this to work. I would like some assistance with what code goes where.
Thank you

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Left() in lookup

Post by pbottcher » 2018-10-18 20:19

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.
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.

User avatar
baudwalker
Veteran Member
Posts: 188
Joined: 2015-02-03 08:08
Location: Bellingen NSW Australia

Re: Left() in lookup

Post by baudwalker » 2018-10-19 08:15

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.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Left() in lookup

Post by pbottcher » 2018-10-19 18:49

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
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.

User avatar
baudwalker
Veteran Member
Posts: 188
Joined: 2015-02-03 08:08
Location: Bellingen NSW Australia

Re: Left() in lookup

Post by baudwalker » 2018-10-20 05:03

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

Post Reply