Simple Find/Create Related Record?

[LaRetta]

Okay, this one must be so simple they don't put it in Help and I couldn't find any posts on it using search!! This is my first attempt at this, so could ya'all walk me through it?

I have a Client db related to a CPMS db. In Client, I have a form with a CPMS::CPMS# on it. Right now, when I want to create a new related CPMS record, I open the CPMS db and add a new record, type in the related ClientID, then re-open Client. Whew! I'm trying to write a script for this. I changed the CPMS::CPMSID field on my Clients form to 'do not allow entry', placed a transparent button over it and tried to write a script that would: 1) Open CPMS, 2) Perform find on Client:ClientID, 3) If found, GoTo Form layout or 4) If not found, create new record, insert related ClientID in CPMS::ClientID field. I figured out that I have to first write the script in CPMS, because in Clients, my CPMS layout wasn't even listed (smile). But when I try to write the 'Find/New script in CPMS, I can't figure how to [copy] the related Client:ClientID to [find], much less [paste]. Please help me write my first 'related' script?

[kenneth2k1]

Hello:

In order to create a related record in CPMS, you can write a script in Client:

Copy [select, "ClientID"]

Perform Script [external **script listed below**]

Also make a script in the CPMS db that shows the CPMS file:

Refresh Window [bring to front]

New Record/Request

Paste [select, "CPMSID"]

Go to Next Field

That should create your related record, BUT there is an easier way to do this through portals and checking "allow creation or related records" in your relationship definition and then you can enter a new record in the last row of the portal.

I haven't tested this thoroughly, it's off the top of my head. So I might be wrong

Ken

[LaRetta]

Thanks Kenneth! I will try that. I want to be sure the record doesn't exist first by using a [FIND], I think. I'm not sure about how to paste from a clipboard (?) into a find script, but at least I'll have the ClientID, right? I'll play with it. After all, how hard can it be

The CPMS form is a large form with much data to enter. I want users to goto that form instead of a portal. Then, hopefully it's easy to close CPMS and go back to the Related Client:ClientID? I'll find out when I get that far! Also, I don't know if it matters, but the CPMS# is an auto-enter serial number. Thanks much!

[LaRetta]

When I clicked on the CPMS field in Client, it said "No record found. To create a new record ..."

I had attached the script to field, ... wait, I don't need a transparent button do I? Couldn't I just attach the script directly to the field? Duh, anyway, here's the script, incorrectly attached to a button: Perform Script, Exit:

How embarrasing! I just tried to paste my script example here, and the ClientID number pasted instead!

One more time ... Sorry this is so long ...

SCRIPT IN CLIENT Named: GoTo/Create CPMS

Copy [ ClientID ]

[ Select entire contents ]

Perform Script [ Filename:

[LaRetta]

Do I have to use a portal to take advantage of 'auto creation of related records?' That sounds easier! This CPMS # is a field on a form with other Client information. It's small. And needs to take the user to a CPMS form with CPMS# auto-entered.

[CobaltSky]

Hi LaRetta,

I think you might be better off coming at this from a different angle, and not using scripts for it. As Ken said, it can all be done with relationships instead.

It all hinges on the relationship between your two files. There are two issues. One is the question as to what they key fields the relationship is based on are. The other is whether the option to "Allow creation of reated records" is enabled for the relationship to the clients file in the "Define Relationships..." dialog in CPMS.

Since I gather the the relationship is one to one rather than one to many, you don't even need a portal to do this. How it works is that you need to display at least one field (not the key field) from the related (client) file on the layout in your CPMS file. It must be a required data (not calculating) field so that it will always have a value in child (it can even be a checkbox field made especially for the purpose if you wish).

When you display the layout in the CPMS file, the client file will automatically be opened so that the related values can be displayed.

When you enter a value into the key field in the master file, if there is an existing related record in the client file, the required field will instantly display the corresponding value. If there is no related record, then entering a value in the (empty) related field will create one, automatically entering the appropriate key field value into the new record in the client file.

Having either ascertained that a related record already exists - or created one by the above method, you can jump to that record in the related file very easily. This can be done in a single step without a script - all you need is a button attached directly to the 'Go to Related Record' command (specifying the relationship to clients from the pop-up menu of relationships that appears in in the "Specify Button..." dialog when the Go to Related command is selected).

When users click on the button, the client file will be brought to the front and the related record will be displayed.

I hope you'll agree that the above method is a lot simpler and more direct that what you're wrestling with at present. It can certainly be done the other way (in fact there are several other methods that can be made to work) - but in my view it is better for a whole host of reasons to use simple direct and automatic methods when they are available.

[LaRetta]

CPMS Define Relationship is set to Allow Created Record now. The CPMS form, when the CPMS# is assigned, MUST contain the Client DOB. So if I place the Client DOB on the CPMS, it will just do it? Could I attach this button transparent over the CPMS field itself? And leave it set to 'do not allow ...?" Hmm, I have to think this through. I there is there are much greater implications to this that just creating a new record without script. It's very clean and easy. ALWAYS cross-place a non-key 'required' field in the other db and it'll always work?

[LaRetta]

I'm not sure I would always have a 'required' field that isn't a key field. Could I define a text, auto-enter, value 'N' or something and just always place that in the other db?

[CobaltSky]

Hi,

The only reason that the value should be a required value is so that you will be able to see if there is a related record as soon as you enter the key into CPMS (if the field is blank then it will appear as though there isn't a related record when there is - though this is not a problem as such, because if you then enter a value it will go into the existing related record anyway).

Surely there must be required fields in the client file - for example, I'm guessing that you don't have many client records without a name in them? It sounds like Client DOB would do just as well.

But notwithstanding that, if you want to create an extra field for the purpose, I'd suggest making it a checkbox field with a single value value list of 1. Then if there is a related record the checkbox will have a tick already, and if there isn't then clicking on it will create one.

And yes, cross placing a non-key value into a related db field will ALWAYS create a record if one does not exist, provided the "Allow creation of related records" option is enabled for the relationship. Essentially that is what that option does.

[LaRetta]

I thought about name, but I didn't want users to have to go to the CPMS form to enter the name. This is exciting, I'm going to have to play with this A LOT until I thoroughly understand the concept. This will save so much time! Well, the CPMS# is required, but we don't add those records until ... well, actually they could be added anytime, right? Geeze, let me have a think on this one!!!

[LaRetta]

A CPMS form is required on EVERY child. The CPMS # is auto serial. So there should always be a record created!

[CobaltSky]

You can use name if you wish. Users won't have to go into CPMS to enter the name.

Because the system is relational, the name field can appear in both places, and can be entered/edited from within the client file if you are in the client file, or entered (still into the field stored in the client file) from on the CPMS layout if you are in CPMS.

In this way, the relational structures can be very elegant and seamless - and can indeed save bulk time!

[LaRetta]

YES! I get it! No, actually I don't. The Client file must have LName and FName fields. I could put them in CPMS, and place the CPMS:LName etc. on Client, but ... it DOES matter where the field originates from, doesn't it?

[LaRetta]

Sorry I'm taking up so much posting space! I really want to get this!! Maybe the DOB, because the DOB is a required field in CPMS. In fact, many of the fields in Client are required in CPMS and I was wondering if I should 'move' them or where they really should reside. But name? I use that in calcs in Client!

[CobaltSky]

My suggestion woud be to only store the clientID in CPMS. You can pick up all the other data about the client from the source in the related client record.

[Ugo DI LUCA]

Hope you find a solution now. If not here is the solution Andygaunt just told me. Works great:

You can use

If [ IsValid (Your relationship::The identifying field ]

Then continue as if they do exist

Else

do something here if they do not

End If.

IsValid will check the related file and if there are records that match the relationship will return a true response.