Having one table update the other table.

[chuckcou]

Just moved up from calculation. It slow learning, but I am getting better each day. Anyway, I am stumped on this one.

{attached coutniesexample.fp8)

The details>>

Two tables

1st Table>[color:red]counties([color:green]id_state,county,statecode)

2nd Table>[color:red]States([color:green]id_state,state_short)

The 1st Table [color:red]counties has no info in [color:green]id_state. What I want to do is put the [color:green]id_statedata number from the [color:red]States table base on [color:green]state_short=statecode

I tried an import and it just put the data at the end.

I was thinking, I could do a find and replace,but that would mean I would have to do it 52 time. :

Thus, I would think maybe a script, but I am not sure where to start to how to solve this problem.

Some examples would be greatly appreciated.

At least I can do the research on what the example is doing. :yay:

coutniesexample.ZIP

Edited October 15, 2006 by Guest

[Søren Dyhr]

I thought I was going nuts, until I discovered that the state abreviation weren't identical in the two tables, one with tailed spaces and one without, so a scripted replace was needed using:

http://www.filemaker.com/help/FunctionsRef-381.html

Anyway, the issue is that such things should be copied at all, but shown in a referenced field pulled in from a relation away.

--sd

coutniesexample.zip

[chuckcou]

I have been reading this morning and maybe I shoud do a script with find and replace, or a lookup? Hum, not sure?

If you had two tables and you need to fill data into one of the table from the other table what would you use? Maybe this will get me looking in the right directions?

[Søren Dyhr]

If you had two tables and you need to fill data into one of the table from the other table

This is conceptually wrong if what we do should be categorized as a relational database. The wish with relational databases is to avoid redundance and your suggestion, is although obedient - wrong!

--sd

[chuckcou]

This is conceptually wrong if what we do should be categorized as a relational database. The wish with relational databases is to avoid redundance and your suggestion, is although obedient - wrong!

--sd

Søren Dyhr

Thank you for the response. This solution is to easy.(expect for the trail space, sorry about that. Filemaker pro need to allow you to turn on hidden character, thus you could see the problem sooner.)

I am evaluating why I didn't think of this way.

It appears our post were less than a one minute apart. Thus, I am just seeing your post for the 1st time.

Thanks again for the feedback. :thanks:

[chuckcou]

Well, I found the space problem, too. I didn't want to just copy your example without understanding it. :

Can you remove the space within the same field? I know I can create another field and do a trim command and it will work. But I would rather just correct the [color:green]state_short.

Thus [color:green]state_short field has a space after every state>>example: CA(space character). [color:red]I want to remove the spaces after every state.

Thus, I would use this function Trim([color:green]state_short).

So, I tried.

Under field the calculation under auto_enter, I tried Calculated Value>>>[color:red]Substitute ( [color:green]state_short ; [color:green]state_short ; Trim([color:green]state_short) )

But when I looked at the data under [color:green]state_short it still has a space.

Any ideas?? :bang:

[chuckcou]

Opps, :oops: I got it. Instead of under field, calculation, auto_enter.

I did records, replace field contents and then [color:orange]Substitute ( state_short ; state_short ; Trim(state_short) ).

Actually now that I write this I could have just done [color:orange]Trim(state_short) :woohoo:

[Søren Dyhr]

Take a look at this script function:

http://www.filemaker.com/help/Script-Steps52.html

--sd