lookups and updating field?

[datacaster]

Hello,
I'm a bit confused here being newbie where I have a "Sites" table in which there is a field called "Site_Name" . In the table I do a lookup using field rules so the site names are in a drop-down list or popup in all my forms that reference the various site names. In another table I have a field called "Item_SiteName" to select the proper site name which is appropriate for that item.

I assume it is because the field record is now stored in a different table, but if I go to the top table in sets which is the Site table and change the spelling in the "Site_Name" field (My Sites Form), it does not cascade to the next table that I just saved the record in the above description. If I click on the field in my (Item form) that references the saved record "Item_SiteName" (It is displaying old spelling), the correctly spelled name is there in the drop-down list and it updates once selected saved, but there must be a better way of doing this. I want any top level changes in top table to cascade down to all the children that reference it without going to every record and doing the drop-down to update each record.

So, I have a table called "Sites" with each Site Name location and the unique ID number field called "Site_ID" and the other table item with a field for linking called "Item_SiteID" with a one to many relationship and referential integrity set to cascade changes. (Note:I have many table that use the site name drop-down list in their forms) Also each table has a field that matches "Site_Name" and "Item_SiteName". I do not link on these as I get an error message and forms do not open.

Any help is appreciated as usual I am lost here.

Thank you!

[MoGrace]

Re: lookups and updating field?

Hi Paul,
The best practice for the forum is to upload the tables with some sample data so we can see your situtation too. Otherwise a screenshot is helpful.

[armasoft]

Re: lookups and updating field?

Paul, if you go to 'edit field rules' for the sites table, then from the 'rules' menu 're-evaluate rules' thnis should refresh your exisiting data.

I found it hard to follow your post, but I think this may be what you're looking for.

[MoGrace]

Re: lookups and updating field?

Hi Jon,
I think he wants to pass multiple fields from the parent to the child in a set-based form.

He could try linking the set on multiple fields, though I am not certain this would accomplish what he wants or not.

[Stan Mathews]

Re: lookups and updating field?

Database normalization suggests you don't store something like site name in multiple tables for precisely this reason. You assign each site a unique id of some sort and have one field in one table for the corresponding site name. Any linkages/references to the site in other tables are on the id. If it is desired to display the site name in the context of any of the other tables a lookup to the site table can retrieve it at the time it needs to be displayed.

[MoGrace]

Re: lookups and updating field?

Stan,
I was going to suggest that he use a calc field for this, but since he hasn't responded back yet, I thought that maybe that should wait...

[datacaster]

Re: lookups and updating field?

Thanks for all the responses to my confusing question/issue!

I'll try to explain better tomorrow if this does not help. I probably need to draw a diagram.
I am sure I am using wrong method to do this if I could only explain clearer.
I am using unique id's in each table with referential integrity set to cascade changes.
Site table has UID field name Site_ID and item table has UID Item_ID. I link on this between the two tables
In the item table Site_ID is stored in the field Item_SiteID
I use a table lookup in the field that I want to pick the site that the item is at. It is drop-down lookup using site table to fill the form that is using the item table. I use a type in field for drop-down lookup.
I have more tables downstream from that and if I delete an item the child tables under item table are all cleared so no orphan records are left in child tables.
If I delete the site, all records in other child tables referencing the site_ID are removed and no orphan records exist in child tables.
If I make the change on the parent table, the type in field of the other tables stays with old name as it was saved to that field and I have to click drop-down to see corrected name, select, and save.

Thank you everyone for trying to help out!

[armasoft]

Re: lookups and updating field?

1. "Site table has UID field name Site_ID and item table has UID Item_ID. I link on this between the two tables In the item table Site_ID is stored in the field Item_SiteID" - Surely the link is between Site_ID & Item_SiteID

2. Why would you want to change the Site_ID in the parent table?

I'm not trying to be awkward, just trying to understand your thinkiong.

[datacaster]

Re: lookups and updating field?

I never change the UID on any table once the record is entered as it is the reference to each record parent to child in my sets.

Maybe I should take a different approach as I was going to the "item form" straight away after entering in the sites and using the drop-down to select which site it was at.

I just now changed it where I go to sites and choose the site I want to deal with (The site has data details, etc. of that location). I have a link on the site form that I have used action scripting to get the site name and site id into variables called siteid and sitename. I open the item form and insert those variables into the sit ID field and site name field, so they are pre-populated at the new record start.

The catch is the site name is just a convenience like a domain name and easier to remember than an ID number. Site ID 1 might be Boston and site ID 101 Las Vegas.
The address and all other data for that site is in the site table. Now I buy an item for site id 101 Las Vegas, the Las Vegas field is just there on the form to make it easy. I really shouldn't store the site name in the item table as it is redundant data, but not knowing anything about scripting, I probably could just have the name pulled from the site table since the site id is known and display on the item page for convenience.

My problem is if I make a change in site name like Las Vegas to LasVegas or Las-Vegas which is the only reference I use for convenience does not change on my other forms as the previous site name from top parent has been stored into the other table which is redundant and not really needed since site id is stored. I don't know how to do it any other way at this moment due to my ignorance in programming on how to take that site id number and get the text of the site name to display on any forms that use it. It would then reflect the change in spelling since it is not stored on any table but one (the top parent table site).

Thanks again everyone for reading my bunch of confusion, but you are helping make me rethink things.

[Stan Mathews]

Re: lookups and updating field?

Like this.

'example from interactive window
[COLOR="#FF0000"]current_site_id[/COLOR] = "00002"

? lookup("sites","site_id = "+s_quote([COLOR="#FF0000"]current_site_id[/COLOR]),"site_name")
= "Las Vegas                     "

sites table.png

[datacaster]

Re: lookups and updating field?

Hi Stan,
So this single line of code goes where to display the site name?

Thank you,
Paul

[Stan Mathews]

Re: lookups and updating field?

If you are on a form, for instance, based on another table where the site_id is a field in that table (say the site_id in this second table is "Item_SiteID") you can create a calculated field for the form and place it on the form. The expression for the calculated field would be

st_name = lookup("sites","site_id = "+s_quote(Item_SiteID),"site_name")

[Tom Henkel]

Re: lookups and updating field?

I would make it a calculated field on each form that needs it , or possibly a global variable that could be referenced on all the forms.
the global variable might be more efficient as you would only be doing the look one when you change sites.

Tom

[datacaster]

Re: lookups and updating field?

Thanks again Stan!

That does what I need it to do!

Thank you all for your responses.

Paul

[datacaster]

Re: lookups and updating field?

"I would make it a calculated field on each form that needs it , or possibly a global variable that could be referenced on all the forms.
the global variable might be more efficient as you would only be doing the look one when you change sites.

Tom"

Hi Tom,
I just did that on a couple forms to test and that works based upon Stan's input and changing to the table it uses in the form, and I see the variable and global tab, but like before I have no clue as to what code needs to go in the variable/global panel to achieve what you say.

I've just started using this program last week and before that I used Access 20 years ago a little, so now I am getting older it gets harder to grasp everything. This is my retirement keep my mind going plan I hope along with PIC chip programming learning.

Thank you!