Form Logic Events - Update values when using Lookup Append if not found

[Tim Harvey]

Is there a technique to update the values in another session for a record that is appended when not found during a lookup?
Lookup options setting "Append if not found" will create an new record in the Source session if it does not exist when Submitting the current form. But I cannot find an Event that will allow me to update any values in the new record using SQL. SQL statements like Update Session1 Set Session1.Field18='Y' Where Session1.Field1='[*0*]' don't work because when it is run the new record doesn't yet exist.

Also as a side note when you use Lookup "Append if not found" to create a new record in the Source Session default values for individual fields do not get set in the new record.

 

[Corey Hagewood]

Hi Tim,

It sounds like the logic might be triggering before the submit action. (In this case the submit action is creating the new record with the Append if not found lookup function.) I am not sure if you can get the update to trigger when you need it to. In this case it might be better to not do an update, or an Append if not found. Instead do an Insert into the other session using a SQL logic item. This should let you trigger it when ever you want to. You can also specify the values you need defaulted in the SQL statement itself, so that solves the other issue as well. So instead of appending the record then updating it, you would just be inserting it with everything you need in one go.

Let me know if you have any questions on this.