Combo Script to Capture a 2nd field from Another Table

[Garth Groft]

the desktop app form has dropdown combo for warrReg.customer_installer field. the dropdown list displays both companyname and custcode fields from a customers table. customers.companyname is all that is needed to populate warrReg.customer_installer. this is working.

now i want to set warrReg.custcode equal to customers.custcode on the customer record selected from the list. i can not find an example or discussion of this simple concept anywhere. this is no doubt a common need and easy to do. i have tried many variations on this script, the last one leading to "table not in change mode". the script that makes sense to me is simply one line of code: warrReg.custcode = customers.custcode. ??? opening these two tables does not make sense as i would think they are are already open. ???

OnChange Event script:


'Date Created: 16-Apr-2015 03:12:16 PM
'Last Updated: 16-Apr-2015 03:12:16 PM
'Created By : Garth
'Updated By : Garth
debug(1)
dim cust as p
dim customers as p
dim warrReg as p
'cust = table.open("customers",FILE_RW_SHARED)
'cust.index_primary_put("cmpnynm")
'warrReg = table.open("warrReg",FILE_RW_SHARED)

warrReg.custcode = cust.custcode
warrreg->CUSTCODE = cust.custcode

BTW: when i remove the first four lines of code, they are back next time i open the script. ???

[Stan Mathews]

Re: Combo Script to Capture a 2nd field from Another Table

To reference a currently open table, http://wiki.alphasoftware.com/TABLE.CURRENT+Method

Since you're dealing with an open form you probably want

warrReg =table.current(#)'where # is the slot
'or warrReg = table.get("warrReg")
warrReg.custcode = customer_installer.value

You can turn off the automatic script change date time stamping by using the

View menu
Settings
Preferences
Editing Scripts
Date-Time Stamps

[Stan Mathews]

Re: Combo Script to Capture a 2nd field from Another Table

Also, since you're dealing with an open form, you can place the warrReg.custcode field on the form and hide it. Then the onchange event script is simply

custcode.value = customer_installer.value

Assuming custcode is the object name of the custcode field.

[Garth Groft]

Re: Combo Script to Capture a 2nd field from Another Table

the script is still not pickling up the customer custcode. ?????

regarding the line of code: warrReg.custcode = customer_installer.value
i have two values - one used by the the combo itself, the other by the custcode field.
warrReg.customer_installer needs to be captured from customers.companyname.
warrReg.custcode needs to capture the custcode value from customers.

revised script
'dim cust as p
'dim customers as p
dim warrReg as p
'cust = table.current(1)
'warrReg = table.current(1)
warrReg = table.get("warrReg")
warrReg.custcode = customer_installer.value

[Garth Groft]

Re: Combo Script to Capture a 2nd field from Another Table

i need to display custcode field captured from customers table when using the customer_installer combo on the form

[Garth Groft]

Re: Combo Script to Capture a 2nd field from Another Table

i changed the last line to:-custcode.value = customer_installer.value

somehow this script is now working!! even though there are two values: customer_installer and custcode in the combo.

[Stan Mathews]

Re: Combo Script to Capture a 2nd field from Another Table

custcode.value = customer_installer.value

This line says

take the value property of the object on the form named customer_installer and place it in the value property of the object named custcode on the form.

Really has nothing to do with the combo box values other than being triggered by the onchange event of it.

[Garth Groft]

Re: Combo Script to Capture a 2nd field from Another Table

actually i see now that my script is still not working.

using custcode.value = customer_installer.value,
warrReg.custcode on the form is not being replaced with customers.custcode. warrReg.custcode is being replaced with customers.customer_installer which is from customer_installer.value selected from the dropdown list. i need to capture the custcode associated with customers.companyname (i.e. the combo list)

dim cust as p
dim customers as p
dim warrReg as p
'cust = table.current(1)
'warrReg = table.current(1)
warrReg = table.get("warrReg")
custcode.value = customer_installer.value

[Stan Mathews]

Re: Combo Script to Capture a 2nd field from Another Table

I think you'd be better served with a table or form level field rule lookup than the combo box. No coding required.

[Garth Groft]

Re: Combo Script to Capture a 2nd field from Another Table

stan, thank you for your assistance today.

i was expecting that scrolling the combo drop down list was moving the record pointer in the customers table. if so, then i could grab Customers.custcode from the record and assign it to warrReg.custcode. so are you a saying that Alpha combos cannot do this? could a fetch move the pointer.

is there no command that allows me to capture the custcode value associated with the customers.companayname field displayed in the drop down? can i not take advantage of the fact that custcode values are included in the dropdown. is there no way to capture the 2nd column in the combo's record list.

i do not know if i could accomplish what you suggested without considerable assistance. plus the client may likely object to no longer using a combo. they want to see all the choices when adding / editing the customer_installer field.

[Stan Mathews]

Re: Combo Script to Capture a 2nd field from Another Table

i was expecting that scrolling the combo drop down list was moving the record pointer in the customers table

Don't think so. I believe the combo box control extracts values from the source table to display a list of values. Any columns other than the leftmost are aids to choosing the correct record/value.

The table level table lookup is much the same visually as a combo box but you can fill multiple fields.

Take a look at the invoice _items table in the AlphaSports sample database. The product_id field is an example. Pretty straightforward.

could a fetch move the pointer.

To code it you need to use the value obtained by the combo box, get a pointer to the customer table, find the combo box value in the customer table (filter for it or fetch_find() the value in an index), get the value in the companyname field, store that to the proper form field.

Thinking a bit further, the onchange event code could have a line

cname = lookup("customers","custcode = "+quote(custcode.value),"companyname")

Then use the cname variable to store to the warrReg table. Saves all the table fetching.

[MoGrace]

Re: Combo Script to Capture a 2nd field from Another Table

Hi Garth,
In the image above you have the result value to be returned as COMPANYNAME change that to CUSTCODE.

[Garth Groft]

Re: Combo Script to Capture a 2nd field from Another Table

in reference to the "image above", are you saying change:
cname = lookup("customers","custcode = "+quote(custcode.value),"companyname")
to:
cname = lookup("customers","custcode = "+quote(custcode.value),"custcode")

[Stan Mathews]

Re: Combo Script to Capture a 2nd field from Another Table

Unzip to a new folder and test.

test.zip

[Garth Groft]

Re: Combo Script to Capture a 2nd field from Another Table

that's cool, but could we reverse the two fields so that customers.company name is first and custcode is last in the drop down? cust code is just going along for the ride.

the user does not typically know custcodes very well, but do know company names.