Formula on the Trigger Level

[kpcollier]

I have a Client Table and a Work Order Table. The Work Order table has a form where you select a client, which provides that client information such as name and address, and says what work needs to be done. I am working on a workflow where if you're filling out a Work Order Form for a client that is not in the Client Table, a trigger will create a new record for that client. 

In the Client Table Form to manually add a new client, there are fields for First Name and Last Name and fields for Address, City, State, etc. There are also formula fields on the table level that concatenate these into one field - Full Name and Full Address. This is where my problem starts with this new workflow.

When entering in new customer info on the Work Order Form, they enter in a full name and a full address. I need to be able to split up these values into multiple fields. In example, the Work Order Form will record a Full Name value, and I need the trigger to split it into First Name and Last Name. I could do this on the table level and make First and Last Name fields a formula, but then they are non editable and totally cancels out the way my Client Table form and Client Update form works. 

Tldr; I need to be able to create a formula in a trigger that will split a field into multiple fields, and I cannot do it on the table level because it makes those fields uneditable. 

 

If there is a better way, please let me know!

 

[Hastur]

Hello @kpcollier

It is not possible to implement such a workflow on the trigger level because triggers have no tools to define the exact part you need to save in separate field.
I can suggest you to split the Work Order submission form. In this case, it will be possible to implement such a trigger without any difficulties.

[kpcollier]

Thanks, @Andrew. What do you mean by split the Work Order submission form?

[Hastur]

@kpcollier

I mean that you may have the same set of fields in both "Work Order Form" and "Client Table" tables.
In this case, you can use Trigger to copy values in appropriate fields.

[DefinitelyNot31337]

First, I'm not sure why you had to do Full Name and Full Address on your Work Order table when you can structure it the same way as your Clients table (with First_Name, Last_Name, etc... on separate fields).

 

===

On 10/1/2019 at 1:07 AM, kpcollier said:

I could do this on the table level and make First and Last Name fields a formula, but then they are non editable and totally cancels out the way my Client Table form and Client Update form works. 

 

I haven't reviewed the table design and workflow as well. Haha. But I wanted to mention that formula field values can be referenced in the trigger (Read-only).

This means that if you are able to separate the fields you need on a formula field, you may simply set First_Name_Text255 = #inserted.First_Name_Formula on your trigger.

 

Hope this helps

-DN31337

[kpcollier]

On 10/5/2019 at 3:14 PM, DefinitelyNot31337 said:

This means that if you are able to separate the fields you need on a formula field, you may simply set First_Name_Text255 = #inserted.First_Name_Formula on your trigger.

Thanks, @DefinitelyNot31337. I came into this project with it already done and with many problems - I am trying to fix it the best I can. I don't think the Client side and the Work Order side were originally made to work together, so they seem to be completely different. I have since changed the workflow so they both use first name and last name, and the same for Addresses.

I was looking for an easy way and ended up just changing it to the way it should be. I'll keep this trigger info in the back of my mind, though. I appreciate it.