Creating records in related DB if not present

[quilty]

Hello, this is my first post!

I've got a three related databases. One has names. One has jobs. Many people do multiple jobs so there is also a relate DB that joins the information from the two. When the user enters a name in the relate DB, there is a lookup that fills in other data about the person. However, I'm constantly having to check whether a person is in the DB (and if not, add their name) beforehand or the required information would be looked up.

So, what I'd like to do is this: when I type a name in the "relate" DB, it will check the name DB. If the name is not present, it will add it and fill in the lookup information.

There is an option for "Allow creation of related records" when setting up the relationship. However, this only seems to work with portals.

Any suggestions? Is this even possible?

Thanks,

Jer

[ESpringer]

Welcome, Jer...

I'd recommend (1) using a drop-down list of names in the join file (what you call the relate DB), based on a value list of the names in your person file. That way, you can keystroke the first few letters, see what you scroll to alphabetically, and know whether you're entering an existing value. (Hit escape and type the new name if not).

(2) Using remote fields directly from your join file. Although it's fine to draw information into the join file with lookups, sometimes it's more flexible simply to let the join file SHOW the data fields from the other database. If you SHOW the related fields directly on your layout in the relate file (instead of, or in addition to, looking it up), and "allow creation of related records" is turned on, then (A) when there is a related record, these fields will show the correct values, and ( when these fields are empty (because there is no person record yet), you can just go ahead and enter into these fields, and a record with the right sort of data will be created.

P.S. Make sure that the remote key field in the persons db is not a calculation (NOT a calc field joining first and last name, for example), or "allow creation..." won't work.

Some others here might also suggest that you eventually work on having person IDs instead of just names, but that's an issue different from the one you're inquiring about.

Welcome to the forums!