Passing Virtual field to Actual field

[Colnocode]

As is obvious, by my member name,  i am not a programmer ( naturally that is why I selected Caspoi!) - So from that please read  warning dummy ahead  and 'extra explicit please'

I have a data page that mostly works real well.  Just one area has crossed me over:-

I have in a simple Table(1) and it has just two fields an ID and a Message (text 6400). I have a second Table(2) and it also has amongst many other fields,  a field also called  Message (text 6400).

My data page has data source of Table(2), it is a form. On entry to the data page a parameter comes with @Mid (message id for Table(1)). I use a virtual field [ Virtual3], with Form Element 'Cascading Text Field' and @Mid to select the appropriate 'standard message' from Table(1).  Sometime it is necessary to adjust/change/ personalise the message, the virtual field accommodates that very well. So far, so good no issues.

Final, when I press submit ,I want the data (changed or otherwise in Virtual3) to be saved in new Table(2) record in Table(2) Message, but can't figure out how to do that (if have seen some scripts that maybe can do this but I am not a programmer so I am not sure?).

 

Happy to answer questions 

 

Thanks in advance for your help

 

[Vitalikssssss]

Hi @Colnocode,

Why you cannot use the Message (text 64000) field from Table(2) instead of [ Virtual3] if you want the data to be saved in Table(2) upon submit?

Regards,

vitalikssssss

[Colnocode]

13 minutes ago, Vitalikssssss said:

Hi @Colnocode,

Why you cannot use the Message (text 64000) field from Table(2) instead of [ Virtual3] if you want the data to be saved in Table(2) upon submit?

Regards,

vitalikssssss

Good question vitalikssssss. More explanation may help.  What I am trying to do seemed so simple - but!

So,  I have many semi standard email messages (Text 64000) -Table (1). Simplistically, I want to choose the (most) appropriate email message and edit it (without spoiling the original) and then send the edited email message.  

In reality, I have a data page  that 'sends the emails' as well writing to Table (2).  Other data pages in the work flow pass the standard email number (@Mid) and another parameter to my 'send the emails' data page. It is in this 'send the emails' data page that I would like to get the email message (@Mid)  edit it,  saving the updated email message in table (2) and send the updated email.

I hope that makes what I want to achieve cleared. I appreciate your help.

Regards

Colnocode

 

[Vitalikssssss]

Hi @Colnocode ,

Thank you for the details you have sent.

I am afraid the reason that you do not want to use actual field because you will spoil the original email message makes no sense to me.

You can use a standard Text255 field instead of the Virtual field and assign Cascading Text field form element and Table(1) will be a lookup for this field.

Please note that you will not be able to change the records from lookup table within the current design of Caspio. 

You can share with me the screenshots/links to your datapages in PM.

I will be glad to help you.

Regards,

Vitalikssssss

 

 

[Colnocode]

Hi Vitalikssssss,

Thanks for the responses.

You are of course  100% of correct if the standard email messages are <=255 chars (in fact when  I started, I did exactly as suggested,  only to find I could not set up  my 'standard email messages' because they are more than 255 chars . So, I tried to change the field to 64000  and then go stuck) . So the question stands  - simplified even more; how can I copy a 6400 text form one record to another record  (in a different table)?

Thanks for the effort 

[kpcollier]

@Colnocode, I have a crazy way to get around this, because I have had to do it too.  

First, I had to create some sort of action on the page to declare a change. This would be something like adding a Checkbox field, where if the message is to be edited, the user Checks the box and then proceeds to edit the message.

If what you want is one Text (6400) field to be copied to a Text (6400) field in another table, you can use a trigger. 

You could use On Update Data event.

INSERT INTO (select the table you want it copied to). -*Click the Cog symbol on the INSERT INTO action and check SELECT. 

SELECT FROM (insert the table you are copying from) -*Hit the Cog symbol and make sure FROM, JOIN, WHERE is selected.

INNER JOIN (select #inserted) ON (#inserted.ID equal Table1.ID)

WHERE (#inserted.editcheckmark equal true)

Then, on the left menu of the Trigger Setup, select Data and drag over Field. Do this for as many fields you wish to be copied over to the new table. 

With this, every time a record in your first table has that box checked and then edited, the message will be copied over to a new table along with all other fields you select in the trigger.  If you try to implement this without the checkbox, however, I believe every record in the table that has the Message field empty will copy over. 

If you choose to implement it this way and have more questions, feel free to send me a message. 

Thanks, 

KPC

[Colnocode]

Thanks KpCollier,

 

On 11/27/2018 at 8:25 AM, kpcollier said:

@Colnocode, I have a crazy way to get around this, because I have had to do it too.  

First, I had to create some sort of action on the page to declare a change. This would be something like adding a Checkbox field, where if the message is to be edited, the user Checks the box and then proceeds to edit the message.

If what you want is one Text (6400) field to be copied to a Text (6400) field in another table, you can use a trigger. 

You could use On Update Data event.

INSERT INTO (select the table you want it copied to). -*Click the Cog symbol on the INSERT INTO action and check SELECT. 

SELECT FROM (insert the table you are copying from) -*Hit the Cog symbol and make sure FROM, JOIN, WHERE is selected.

INNER JOIN (select #inserted) ON (#inserted.ID equal Table1.ID)

WHERE (#inserted.editcheckmark equal true)

Then, on the left menu of the Trigger Setup, select Data and drag over Field. Do this for as many fields you wish to be copied over to the new table. 

With this, every time a record in your first table has that box checked and then edited, the message will be copied over to a new table along with all other fields you select in the trigger.  If you try to implement this without the checkbox, however, I believe every record in the table that has the Message field empty will copy over. 

If you choose to implement it this way and have more questions, feel free to send me a message. 

Thanks, 

KPC

I fiddled with this (too much :-) ) - It got me part of the way but my with knowledge of Caspio  I just could not make it do what I want.  The fundamental issue  I could get right, was I could not choose the record (with the 6400 text), amend the text and write the 6400 test in to another record in another table (without changing the 6400 text in the original test  OR saving the original 6400 text  unchanged in the new record and then having to retrieve the new record again to to modify the test.