Submitted by Tom on 2011/03/29 13:27
From -- link to nonexistent node ID 294 --
 
[quote=mjwilliamsmj]
Very good how-to for Auto List of Values!
 
Unfortunately, I cannot figure out any thing else from the posting.
 
I have the following two grids:
 
Grid named ZipCode.
Item is the "the zip code", with City and State fields.
 
Grid named Addresses.
Item is "the name", with Street, City, State and Zip fields.
 
When filling in the Addresses grid, I would like a pop-up to pull from the ZipCode grid, and fill in the City, State, and Zip fields.
I have tried to just get the Zip field to fill-in without success.
The example says to put in the "fields editor" - Item as ZipCode.  I have tried that and as ZipCode, and Zip from ZipCode without success.  Whatever I put in the box becomes the TEXT for the lookup!
The pop-up help, by hovering the mouse pointer, over the 'auto list field' indicates using "SQL ....".
 
Could you post a more detailed example of the above?
[/quote]

Comments

 
I have tied myself in knots with the same stuff so not sure how much I can help
 
But, first of all, you say:
[quote=mjwilliamsmj]
I have the following two grids:
 
Grid named ZipCode.
Item is the "the zip code", with City and State fields.
 
Grid named Addresses.
Item is "the name", with Street, City, State and Zip fields. [/quote]
 
I'm wondering why - when you have a field with the zip info in it, why transfer it to the item field of another grid?  - I mean you dont have to tell me(!!) if you have good reason for it - but I'm wondering would this work:
You could use the exact same fields in two different grids:
 
Create the Address grid.
Then, recreate the Address grid as a new grid but with the same fields, [edit] source would be ZipCode [/edit] move the zip field column into the first place and move the item field to the last column. You can also make the zip field into the tree field for that grid. If you need more details, for that just ask.
 

Re your actual request:
the Field Editor is listed as Field Management in the menus or Manage Fields (column header context menu, menu: Tools/Manage Fields) (those names should all be standardised)
Under the pop-up list section on the right-side (as shown circled in the Auto-list screenshot above) you add:
 
Select item as ZipCode
 
(you forgot the Select part)
 

mjwilliamsmj

2011/03/29 17:41

Permalink

The reason for the look-up table.
 
The zip codes would be entered with city and state once.  Thereafter, a look-up on the zip code would be used to fill in city, state and zip in an address, thereby eliminating entry errors.
 
An additional question on this matter, would be, could changes in the zip code grid be used to upgrade those same fields in the address grid?

mjwilliamsmj

2011/03/29 18:22

Permalink

I may be missing something here - like the language to do anything!
 
Is there somewhere to find "Select xxx as yyyyy" to understand what is happening.
 
The ZipCode grid is tied to a ZipCode field which is "yes/no".  The item in the grid will be the Zip codes, and the two additional fields are city and state.
This grid will be preloaded.
 
The address database is tied to a Contacts field which is "yes/no".  The item in the grid is a contacts name, with other fields for the address including city, state, and zip.
 
So using the editor on the Zip field, I put in "Select item as ZipCode".  It does not work and crashes IQ - stating that it was not able to run the query.
The idea would be to select the applicable zip code and have it drop that zip code along with the city and state fields.
 
Without a language manual, I "presume" that I am selecting the item field from the ZipCode grid as the drop down menu.  I sense that maybe that is an incorrect 'presumption":)
 
I have written applications in the old Paradox RDBMS, and that colors my thinking.  This IQ seems to have some interesting capabilities, but where is the language documentation?
(In Paradox, like many other RDBMS, multiple tables are linked in 1-1, 1-M, or M-1.  Look-ups are implemented without code.)

KeithB

2011/03/29 19:34

Permalink

See if this file makes things clearer
 

Tom

2011/03/30 06:23

Permalink

Sorry about that - my mistake
 
To copy from the item field in the ZipCode grid to a field in your address grid, add this to the destination field (let's name it Zip)
Select Item from ZipCode
(I just tested that & it works - I had 'as' instead of 'from')
 
In theory you can copy the three fields (Zip, City, State) in one go. [Can someone with more expertise please comment re this?]  (or you can just try it and see - i.e. I havent tested the following!!)
Display the fields in both grids, then add this to the new Zip field:
Select Item from ZipCode, City from ZipCode, State from ZipCode
 
Can someone with more expertise also comment about the proposed structure of the grids (see first post above) - whether it would be better to have the zipcode as the same field in each grid? (instead of having it as the item field in one grid)
 
XP - SP3      IQ Portable - usually up-to-date :-)

Tom

2011/03/30 06:38

Permalink

I gotta say I find the manual page examples for this pretty confusing as well.
 
e.g. in the two examples shown,
one uses:
Select Item as MyContacts from AdrsBook
the other uses:
Select Item as MyContacts
 
Why the difference?
 
XP - SP3      IQ Portable - usually up-to-date :-)

Tom

2011/03/30 06:35

Permalink

[quote=Tom]
In theory you can copy the three fields (Zip, City, State) in one go. [Can someone with more expertise please comment re this?]  (or you can just try it and see - i.e. I havent tested the following!!)
Display the fields in both grids, then add this to the new Zip field:
Select Item from ZipCode, City from ZipCode, State from ZipCode
 [/quote]
 
see "Assign multiple field values at once" on the manual page (-- link to nonexistent node ID 294 --)
in order for this to work you also have to 'Check "Save all listed fields"'
 
XP - SP3      IQ Portable - usually up-to-date :-)

reesd

2011/05/17 21:00

Permalink

[quote=Tom]
[quote=Tom]
In theory you can copy the three fields (Zip, City, State) in one go. [Can someone with more expertise please comment re this?]  (or you can just try it and see - i.e. I havent tested the following!!)
Display the fields in both grids, then add this to the new Zip field:
Select Item from ZipCode, City from ZipCode, State from ZipCode
 [/quote]
 
see "Assign multiple field values at once" on the manual page (-- link to nonexistent node ID 294 --)
in order for this to work you also have to 'Check "Save all listed fields"'
[/quote]
 
This pretty cool, unfortunately it doesn't work if you type the value in. You have to go through the popup. Even if read only is set (whatever that does). Would be nice if we could type it in also...
 
d

reesd

2011/05/17 22:12

Permalink

[quote=reesd]
[quote=Tom]
[quote=Tom]
In theory you can copy the three fields (Zip, City, State) in one go. [Can someone with more expertise please comment re this?]  (or you can just try it and see - i.e. I havent tested the following!!)
Display the fields in both grids, then add this to the new Zip field:
Select Item from ZipCode, City from ZipCode, State from ZipCode
 [/quote]
 
see "Assign multiple field values at once" on the manual page (-- link to nonexistent node ID 294 --)
in order for this to work you also have to 'Check "Save all listed fields"'
[/quote]
 
This pretty cool, unfortunately it doesn't work if you type the value in. You have to go through the popup. Even if read only is set (whatever that does). Would be nice if we could type it in also...
[/quote]
 
See Smart Folders/Fields (Query Equations) - discussion and doc change suggestions for an idea I had on another way to pet this cat (skinning seems so mean :)....
 
d
 
 

I've been trying this for a few hours now and can only get one field to lookup and pull out another field from a separate db.

Like

Select fullName from Members

But I cannot seem to get multiple fields from the members DB like this:

Select fullName from Members, First Name from Members, State from Members

I have the code in the editor > Advanced SQL

 

Where exactly would I place this code?