Auto enter new record into separate database

[ChrisF]

I have a database that I call Directory. We use this Directory to find which database a record is in. Currently I have to import records from all of my other databases manually so that the directory will be up to date. I would like to be able to enter a new record into any of my databases and have that record automatically show up in the directory. I think that I could probably get it done with a portal, but so far have not had any luck. Is that the best way to accomplish this or should I use an alternate method? Any help would be greatly appreciated.

[Steve T.]

Hi, Chris! I kinda asked a similar question a few years back and the consensus was that it is not possible to AUTO-CREATE a record in another db so I could have 2 identical db's (I wanted 1 for network use and a 2nd for web use). Folks basically told me the closest I could get would be to script it. Now, as for your using a portal, I think it depends....

wait... got to switch to another location... will continue...

--ST

[Steve T.]

... it depends... Creating a new record in any of the db's is not a problem, per se, but it can be problematic if there is no corresponding record in your directory. Let's say your directory db is people.fp5. and you have some other db's such as jobs.fp5, jobsites.fp5, etc.

If you create a new record in jobs.fp5 in which you enter the person's employee number (1002), you can see that person's jobs from your directory file people.fp5 in a portal and any new additions to jobs.fp5 will work fine as long as there is a person to affiliate it the job with (person id 1002).

If, on the other hand, you create a new job file for a new employee, that record will not show up in your directory people.fp5 unless you create a record for that person (id=1009 or something) and put 1009 in that newly created job record in jobs.fp5. Any subsequent new job records will then show up in that person's portal in people.fp5 as long as 1009 is in the job record in jobs.fp5.

Hope this helps some.

--ST

[Steve T.]

Gaah.. I just read what I said and it may seem confusing...

PEOPLE.FP5

1001 SteveT

1002 ChrisF

JOBS.FP5

10 Fixing FM Stuff, 1001

11 Create New DB for Buddy, 1001

12 Making Directory Work Right, 1002

13 Sleeping on Dog House, 1003

14 Impersonate Red Baron, 1003

Job 13 and 14 won't show up until we create

1003 Snoopy

in the people.fp5 database

There, that should be better. Sorry for the jabbertalky, folks.

--ST

[pcourterelle]

For this solution why is a script not workable? I have a similar challenge where data from one db needs to be included in another db and a simple relationship or lookup isn't the answer. I have one db that stores contract info and another production info. Ad sales ppl input data into the contract db, including info for production. I have an 'enter' button at the bottom of the page which accesses a script that copies the production info into the production db, creating a new record when necessary and updating data when the record is already available. Allow this duplicates data it also provides some security as ad sales ppl are generally an untrustworthy lot and prone to change infomation as it suits them (a cynical view I know). I created a relationship between the two dbs and then used several setfield commands to copy the data.

Comments?? If there's a better way, I'll gladly do it

phil courterelle

calgary

[Baloo]

Just to make sure I've got this right:

You have one FM6 file (table) called Directory.fp5.

Directory.fp5 stores the Filename and Row of every row in every file across your entire system.

You want to be able to have Directory.fp5 automatically updated every time a user creates a new row in any file within you system?

This is fairly simple to do.

(Assuming your file names are unique)

Set up a calculated field SystemRowID in each table make it a text field and set it equal to:

Status (CurrentFileName) & delimiter & Status (CurrentRecordID)

or

Status (CurrentFileName) & delimiter & your own unique record ID

add a global text field in Directory.fp5 _gSystemRowID

build a relationship in each file to Directory.fp5

(the concatenated field ensures each SysRowID in Directory is unique)

when a row is created in a Db (Db.fp5) run a script in DB.fp5 (user pushes button , check for un committed records on exit, run every night, etc.)

Set Field Directory::_gSystemRowID = SystemRowID

it

[ChrisF]

Thanks for the suggestions. I will start working on this and will let you know if I succeed.

Chris