Problem with relational data.

[Xettio]

I have 2 databases setup, for simplicity I'll call them 'A' and 'B'. 'A' has a relation with 'b', their product id's are connected. There isn't any problem here, It's later, when I added 'Customer Notes' field to database B.

If I add a new record to 'A' then if there was any text in "B's" Cust. Notes field, it'll be copied correctly into 'A".

If I goto a field that was created BEFORE I added the relationship (cust notes in 'b') then the relationship is still empty, (because i didn't add anything to it yet) but if I goto 'B' and enter in notes on that customer, and them go back to 'A' it's still blank.

The only way I can get it to make the connection if to re-enter the data that relates the 2 together, in this case, the customer Id.

Is there anyway to fix this? I've looked through the scripts and tried a few things but nothing worked. It would be great to have one script that would update all the records in 'A' to reflect any changes I make in database B.

Any help appreciated

Thanx,

Colleen

[LiveOak]

We need a little more detail. What is a record in file A? File B? Also, are you confusing "Field" and "Record" in your description ("If I goto a field that was created BEFORE I added the relationship (cust notes in 'b') then the relationship is still empty")? Are you showing the notes field from "B" as a related field or as a lookup in "A"?

-bd

[Xettio]

ok, file 'a' is customer info, 'b' is sales items.

I think I made it more complicated that it needed to be in my orig. post.

If i have the address and stuff in 'a' for jane doe. and the sales info in 'b', for example an auction item like a painting....

If I enter in all the info into jane doe, then I goto 'b' and enter in the info on the painting...

ok so far... everything works fine so far.

I then goto 'a' and enter in the auction number of the painting for jane, everything is still fine...

Now, a week later, I goto 'b' and in the notes field for the painting, I write a lil note saying, "Jane got the painting and everything went smoothly"...

Here's the problem, When I goto 'a' and look in Janes (related) notes field, it's still blank... But if I goto the auction number (again, thats what relates them together) and delete it, and re-enter the auction number, THEN the text field becomes updated...

I have like 70 enteries now, And I have just added the notes field to both 'a&b' so I goto 'b' and enter in all the text, but I hafta go through the whole rigamerole of re-entering the auction number into 'a' to have 'a' updated..

I hope that made it clearer, I was wondering if there was a small script that could do this in one swoop, I;ve monkeyed around with it for a while with no luck.

-Colleen

[MannyRSilva]

If you simply need to automate the manual re-entry of the ID's into each of the records again, I suggest using a script that uses the Loop function. If you are not familiar with the using the Loop function in a script, I have done it many times, and would be glad to list the steps to you later (I'm leaving from work now to go home).

If you've never done it before, also make a backup copy of the database and run the script on the copy first, because an incorrectly written Loop script can hang up the file if you don't give it an "exit strategy".

[LiveOak]

What you need to do to avoid this situation is to create the record in file "B" using a script in File "A" that 1) knows the customer ID by which is the current record in "A" and 2) creates a record in "B" and sets the customer ID field in "B" to the correct value. As an alternative, you can create a portal to "B" in "A" and set the relationship to "allow creation of related records". When you click in a field in the last blank row of the portal, a records with the customer ID will automatically be created in file "B".

-bd

[Gerd Muller]

I guess You are using a lookup instead of a real relation. You mustn't have the note field in "a", but only in "b". Of course, you can place "b"'s note field in a layout in "a" to see the note related to "a". If there are several related "b"-records, you need a portal.

If you use a lookup, and you are looking up "b"'s note field in a field in "a", then "a" is only updated, if the relation field changes.

Gerd

[Xettio]

Yeah, I had it as a lookup only. I am kinda new and never messed around with portals before, but I got a book which explained it much better to me and using the portal it works great now.

Thanx a ton everyone!

Colleen