Revert Record - Proper sequence?

[kenneth2k8]

Hi All: I've been trying to figure this out on my own for a couple of nights now and I haven't fixed it so I thought I would check with people smarter than me!

Problem:

I keep getting error 504 which is basically the primary key constraint I set up on a field.

Purpose:

The purpose of this script is to add a related event from the Planner table when the values in the field "Interests" in the customer table match the interests field in the Planner table. The super script is a save of the customer record, and there is the possibility that the user changes the "Interests" for a customer. When this happens, the logic should go into the Planner table and find events that match that customer's interests (as set in the Interests field), and insert them into the join table called Events. It does this by the use of a portal from the Planner table to the Events table. the events table holds all related events for customers.

I have to use this structure because each event will need to be "processed" for customers to keep track of what events were discussed when the user calls a customer. They will be processed by checking a field.

Problem:

The script to insert related planner events into the Events table is having a problem when the same primary key is inserted into the portal.

This will probably make sense if I give you the script steps:

Allow User Abort [ Off ]

Set Error Capture [ On ]

#Set Globals

Set Field [ Moxie::g_Interests; Moxie::Interests ]

Set Field [ Moxie::g_CustomerID; Moxie::CustomerID ]

#Find Related Interests and mark them

Go to Related Record [ From table: “Planner 2”; Using layout: “PlannerDataForm” (Planner) ]

[ Show only related records ]

If [ Get ( LastError ) = 101 ]

Exit Script [ ]

Else

Set Field [ Planner 2::g_test; Moxie::g_CustomerID ]

#Insert Related Events

Go to Record/Request/Page

[ First ]

Loop

Go to Portal Row

[ Select; Last ]

Set Field [ Events::PK_EventIDCustID; Planner::EventID & Moxie::g_CustomerID ]

Set Field [ Events::FK_CustomerID; Planner 2::g_test ]

Go to Record/Request/Page

[ Next; Exit after last ]

If [ Get ( LastError ) = 504 ]

Revert Record/Request

Go to Record/Request/Page

[ Next; Exit after last ]

Else

Go to Record/Request/Page

[ Next; Exit after last ]

End If

End Loop

End If

The script debugger will pick up the error 101 which I can get past. However the 504 will lock up the script until I cancel it. I know this is happening because the script will go to all related records based on interests, and since the customer record already has that related event inserted into the Events table, it is erroring because it already exists.

I want it to just revert the record and move on to the next Planner event to insert. But my logic is obviously messed up.

I am wondering if my If get lasterror = 504 steps are out of order?

Any insight would be greatly appreciated. After reading this, I know it's totally confusing so if there's something I didn't explain well... please let me know!

Thanks a lot!

[Vaughan]

I haven't followed your logic through completely, but I'm not a fan of working through portals. I prefer to change to the layout of the related records, create new record, set the fields and return to the original layout. (For bonus points you can do all this in a new off-screen window.) This ensures that there is no chance of over-writing existing related records.

[kenneth2k8]

Thank for the response and the suggestion. I will re-write my script to work towards the type of setup you have talked about. Not sure what you mean exactly with doing it off the layout. Forgive me I am still fairly new to FM11. Last version I used heavily was FM5 so I have some old habits.

Thanks again! I will certainly ask if I run into any other snags.