Copy Record from one table to another

[ron G]

Running FM Pro 11 03

Here is the narrative:

I have a simple database consisting of MEMBERS and CANDIDATES.

There is the MEMBERS table and it's TO: MEMBERS_TO_CANDIDATES (See diagram below)

People who want to be MEMBERS must first be CANDIDATES. Upon meeting the criteria to be MEMBERS, I want to copy "most" of the CANDIDATE information over to a new MEMBERS record.

I have a button that drives a script that uses 'copy' and 'paste'. This works but I have read, on this forum, that a better method is to use SET FIELD. The way I have it setup doesn't work.

Go to Layout "MEMBERS (Members)

New Record/Request

Set Field [Members_TO_Candidates::Name_First;Candidates::Name_First)

etc…

Does this fail because I am working with a TO of Members? Against that thought is the fact that a TO IS the same table that it was TO'd from. Yes? But, if I duplicate the MEMBERS Layout and base that new layout on MEMBERS_TO_CANDIDATES, all the fields are NOT recognized? huh?

I am stumbling around and would appreciate your help.

Thanks

[efen]

The fields on the Members-to-Candidates layout should be from the Members-to-Candidates TO, not from the Members TO.

what is the purpose of your link table, Candidates Link? Should there not be a record created in that in this process to link Candidates to Members?

Without capturing the ID of the Candidate record before you create a new record in the Members table that new record has no connection with anything in the Candidate table so it can't transfer/lookup the information via the linking table.

[bcooney]

I have a system with requirements very similar to yours, Ron. Our stages are Applicants and Fellows. People can be applicants many times (one applicantID, but many applications), but once made a Fellow, that is it.

So, I have a People table, an Applicant table and a Fellow table. The Applicant and Fellow tables only have one field, the keyID and a PeoID. The People table have an ApplicantID and FellowID. It's a subtype/supertype structure.

How do people become Candidates? What is that process? Specifically, how do you know whether that person has already been a Candidate?

Ideally, you do not have redundant data (such as copying a person's info from a Candidates table to a Members table). Rather, you set a flag or use a supertype table (People) and supporting subtype tables (Candidates, Members).

[ron G]

The crux of the problem is that I can't get the SET FIELD script step to actually put the desired value from Candidates into Members. I have tried every conceivable combination and yet I can't get it to work. What am I missing?

I have a system with requirements very similar to yours, Ron. Our stages are Applicants and Fellows. People can be applicants many times (one applicantID, but many applications), but once made a Fellow, that is it.

So, I have a People table, an Applicant table and a Fellow table. The Applicant and Fellow tables only have one field, the keyID and a PeoID. The People table have an ApplicantID and FellowID. It's a subtype/supertype structure.

How do people become Candidates? What is that process? Specifically, how do you know whether that person has already been a Candidate?

Ideally, you do not have redundant data (such as copying a person's info from a Candidates table to a Members table). Rather, you set a flag or use a supertype table (People) and supporting subtype tables (Candidates, Members).

All I really need is for the SET FIELD script step to work. When I run debug and execute the script, FM just ignores the SET FIELD command. What is that about?

[bruceR]

All I really need is for the SET FIELD script step to work. When I run debug and execute the script, FM just ignores the SET FIELD command. What is that about?

Set Field works fine; so it is about you not using the script step correctly. For example trying to set a calculated field; or setting a field that is not valid in the current context; etc.

[ron G]

The source and target fields used in SET FIELD are just text fields. hmmmm... Are you saying that the calculation LASTNAME = Proper(LASTNAME) be the source of the SET FIELD not working?

When I run the script below everything 'seems' to work. But, the result is a blank record in MEMBERS. huh? No error messages. Just a blank record.

Update:

OK. This is what seems to work. Goto Candidates. Set $VAR to Candidates::Last_Name. GOTO Members. Set Field MEMBERS::Last_Name; $Var.

The last line of the script returns "". This seems to indicate that unless FM is on a layout, it looses the record pointer for the associated table. Is that right? This is the only explanation I can deduce from the fact that without the $Var, FM puts blank values as the source with Set Field.

This is NOT a very elegant solution. Is there a better way?

[Vaughan]

Are you saying that the calculation LASTNAME = Proper(LASTNAME) be the source of the SET FIELD not working?

It's probably working but not doing what you expect: my guess is that the LastName field is empty (or not relating to a value).

[efen]

You need to specify another variable for the first name when you specify one for the last name and set the first name field with that.

[ron G]

You need to specify another variable for the first name when you specify one for the last name and set the first name field with that.

Thanks. The code you saw is just 'proof of concept' so that I might understand how FM works to do what I want it to do. The 'working' code will be *very* different.

[comment]

The last line of the script returns "". This seems to indicate that unless FM is on a layout, it looses the record pointer for the associated table.

This "pointer" you speak about, it points to the first related record in the associated table (Candidates, in your example). I believe that at this point, the newly related member record has no related record in the Candidates table, therefore the reference Candidates::Name_Last returns empty.

This exercise aside, you really should keep the record where it is and merely change its status.

[bruceR]

The source and target fields used in SET FIELD are just text fields. hmmmm... Are you saying that the calculation LASTNAME = Proper(LASTNAME) be the source of the SET FIELD not working?

There is a list of problems associated with your assumptions.

As for the statement above, I'm not sure what you expect it to do; but the actual result will be 1 or 0 (TRUE or FALSE).

That is, LASTNAME = Proper(LASTNAME) is a logical statement about their equality.