Read from one table and write to another : Justify student abandonment

[Aristotelis_T]

Hi all, I have a table (excel sheet containing a pivot table) where new entries are created automatically whenever a student abandons the course.
I would like to create a way via appsheet to add a justification for these abandonments, saved in a different table.
Hence, read from one table and create records in another table using info from the first table and newly added info.

How can I create that?

Thanks!

[Steve]

Do you want the justification rows added automatically, without user involvement, or will a user be a part of the process?

[Aristotelis_T]

With user involvement.
The user selects the record from the Abandonment table and adds the justification. The appsheet insert a new record in another table (Justifications) with the info of the student, course, month and the justification.

[Steve]

1. Create a new action for the Abandonment table called (e.g.) Justify.

2. Set For a record of this table, choose Abandonment.

3. Set Do this to App: go to another view within this app.

4. Set Target to the expression:

   LINKTOFORM(
     "Justifications_Form",
     "Abandonment",
       [_THISROW],
     "Student",
       [Student],
     "Course",
       [Course],
     "Month",
       [Month]
   )
   

   • Replace Justifications_Form with the name of the form view you want used if you dont want to use the system provided form view for the Justifications table.

   • "Abandonment", [_THISROW] sets the value of the Abandonment column of the new Justifications row to a Ref to the Abandonment row that spawned the justification ([_THISROW]). Omit these if you don’t have an Abandonment column in the Justifications table.

   • "Student", [Student] pre-fills the Student column of the new Justifiations row with the value of the Student column of the Abandonment row ([Student]). Course and Month are pre-filled similarly.

   

5. Create a new deck, gallery, or table view for the Abandonment table called (e.g.) JustifyAbandonment.

6. Set the Row Selected event action for the view to the Justify action created above.

   

7. Display the new JustifyAbandonment view to the app user to allow them to justify an abandonment by tapping on an abandonment entry.

[Aristotelis_T]

Hi @Steve, thanks for the great answer!

I tried repeatedly your code and I could not get it to populate the fields (fields would be empty).
Here is my code that would not work:
LINKTOFORM(
“Justicacoes_Form”,
“Abandonos”,
[_THISROW],
“ContactID”,
[_THISROW].[ContactID],
“Contacto”,
[_THISROW].[Contacto],
“Last_Courses”,
[_THISROW].[Last_All],
“Last_Payment”,
[_THISROW].[Last_Payment]
)

However, when I removed the 3rd and 4th lines ( “Abandonos”, [_THISROW], ) and all the references to [_THISROW], it seemed to work, It populated the fields of the form!!
Here is the code:

LINKTOFORM(
“Justicacoes_Form”,
“ContactID”,
[ContactID],
“Contacto”,
[Contacto],
“Last_Courses”,
[Last_All],
“Last_Payment”,
[Last_Payment]
)

However, though the form seemed populated ok, after completing and saving the form, the record created in the google sheet would not have the values I populated trough the code! (this fields appear empty, though the rest of the fields the user filled-in manually in the form are saved ok!)

Help! Any ideas?
What did I do wrong?

[Steve]

Aristotelis_T:

However, when I removed the 3rd and 4th lines ( “Abandonos”, [_THISROW], ) and all the references to [_THISROW], it seemed to work, It populated the fields of the form!!

Yep, the [_THISROW]. prefixes were my mistake. I’ve updated my instructions to reflect this.

I don’t know why the 3rd and 4th lines wouldn’t work, assuming you have an Abandonment column…

Aristotelis_T:

However, though the form seemed populated ok, after completing and saving the form, the record created in the google sheet would not have the values I populated trough the code! (this fields appear empty, though the rest of the fields the user filled-in manually in the form are saved ok!)

Could you post a screenshot of the emulator in the app editor showing the fields populated correctly?

[Aristotelis_T]

Hi @Steve! Your replies are so educating!

You said " assuming you have an Abandonment column…"
Quick question about the 3rd and 4th lines ( “Abandonos”, [_THISROW], 😞
»»» a) Is Abandonos a table or a column?
In my case Abandonos (or Abandonment as in the example) is a table.

Screenshot:

Current code:

LINKTOFORM(
“Justicacoes_Form”,
“ContactID”,
[ContactID],
“Contacto”,
[Contacto],
“Did_Not_Pay”,
[Did_Not_Pay],
“Last_Courses”,
[Last_All],
“Last_Payment”,
[Last_Payment]
)

The record created in the google sheet is the following:

columns 1 and 3 were filled in by the code and WERE recorded
columns 2, 4 and 5 were filled in by the code and were NOT recorded

Help…

[Steve]

Please post a screenshot of the column list for the table from the Data > Columns tab in the app editor.

[Aristotelis_T]

Abandonos - 1/2

Abandonos 2/2

Computed key: CONCATENATE([ContactID],": ",[Did_Not_Pay])

Justificacoes

Computed key: CONCATENATE([ContactID],": ",[Did_Not_Pay])
Side note: I tried to make ContactID and Did_Not_Pay as Not Editable but I have not managed to ‘convince’ appsheet to it…

[Steve · Accepted solution]

The problem appears to be that the Contacto, Last_Courses, and Last_Payment columns have Editable? set to OFF.

If Editable? is set to OFF, a column value cannot change under any circumstances. In effect, Editable? set to OFF means read-only.

If you just want to prevent the app user from modifying a column value, but want the app itself to modify the value (as appears to be your case), instead set Editable? to the expression, FALSE.

[Steve]

The ContactID and Did_Not_Pay columns have a similar problem: they are effectively read-only (equivalent to Editable? set to OFF) because they are used to compute the row’s key value.

A row’s key cannot change once initially set; consequently, any column used to construct the key value also cannot change once initially set.

You will need to find or construct a different row key if you want to modify the ContactID and Did_Not_Pay column values.

[Aristotelis_T]

Great and Wise @Steve! Fantastic support!
I learned so much from you reply! :
The FALSE option is quite hidden and very important!

I have more doubts but I believe I should post these in a separate thread.
Thanks!