Actualizar datos en tabla referenciada / Updating referenced data in other table

[Lupintam]

————————————— SPANISH (See english version below)

Saludos.

Estoy empezando con Appsheet y no sé muy bien cómo realizar crear una funcionalidad en mi App. En principio, creo que se puede resolver mediante automatización pero también acepto ideas alternativas como modificar mi estructura de datos. Cualquier sugerencia es bienvenida.

Estoy desarrollando una app para generar informes de asistencia sanitaria. Esta app tiene dos tablas principales: Informes y Pacientes. Informes es una tabla maestra que contiene el grueso de los datos y Pacientes es una tabla a la que se hace referencia desde informes.

Un Informe solo puede referenciar a un Id de paciente pero un ID de paciente puede aparecer múltiples informes.

Pacientes dispone de una clave UID a la que se hace referencia desde informes, de tal manera que cuando se genera un informe se puede seleccionar o añadir un nuevo paciente. Esta relación NO tiene marcada la casilla “Is a part of?”

La tabla Informes contiene varios campos incluidos en la tabla Pacientes. La razón de esto es que, para corroborar la correcta filiación del paciente, se necesita mostrar múltiples datos del paciente en el formulario de informe. Estos campos se obtienen de la tabla de pacientes mediante expresiones dereference.

Los valores de los campos raídos de Paciente en el informe se cargan como “Intial Value” para permitir que puedan actualizarse datos del paciente desde ese formulario de informe.

Objetivo
Mi objetivo qué, al guardar o modificar el informe, puedan actualizar en la tabla de pacientes los datos correspondientes al paciente que se hayan modificado (o directamente todos los datos del paciente a los que se hace referencia en Informes).

Creo que esto se podría hacer con un una acción o serie de acciones del tipo Data.

* Data: execute an action on a set of rows
* Data: set the values of some columns in this row

Estoy probado pero no termino de entender los ejemplos que veo. Especialmente creo que me falta la expresión que iría en “Set these columns” de la tabla de Pacientes para traer el dato equivalente de la tabla de informes.

En fin, que cualquier idea, ejemplo o tutoríal sobre este tema seria muy agradecido.
Mil gracias!

————————————— ENGLISH

Greetings.

I'm starting with Appsheet and I’m not sure about how to implement a feature for my App. I think it can be solved through automation actions, but I willing to considerate any alternative approach, such as modifying my data structure or UX. ANY suggestion will be welcome.

I am developing an app to generate health care assistance reports. This app has two main tables: INFORMES (reports) and PACIENTES (patients). INFORMES is a master table that contains the main data, and PACIENTES is a child table which is referenced from reports.

There is only one patient in each report but, one patient could be referenced in multiple reports.

PACIENTES has a UID key that is referenced from INFORMES table, so when a report is generated a new patient can be selected or added. This relationship does NOT have set the “Is a part of?” checkmark.

INFORMES table contains several fields included in PACIENTES. This is because user should be able to corroborate many data from the patient when choosing. These fields are obtained from patient’s table using dereference expressions. Also, data for these columns are load as “Intial Value” to allow updates directly from the reports form.

My goal, is that all patient data could be saved in PACIENTES table when report is saved or modified.

I think this could be done by using Data Actions.

* Data: execute an action on a set of rows
* Data: set the values ​​of some columns in this row

I'm trying this right now but I’m not understanding exactly how it works. I have seen a couple of examples where a column is changed with a fixed value but, Im not sure of which expression should I use in “Set these columns” part in order to load with data obtained from INFORMES table.

Any idea, example or tutorial will be greatly appreciated.
Thanks a lot!

[jyothis_m]

Are you trying to change the column values in PATIENT table when it is changed in the REPORT. Say in REPORT FORM if Patient's age was having initial value as 30 from PATIENT TABLE and when user changes this value to 31 in REPORT FORM and save, you want the AGE Column in PATIENT Table to be updated to 31. Are you trying to achieve this? 

If so you can Create the following ACTIONS

1. UPDATE_PATIENT in PATIENT TABLE with SET VALUES OF SOME COLUMNS IN THIS ROW

Select the Columns of PATIENT Table that you want to update and give value as INPUT() Function

INPUT() - AppSheet Help

2. CALL_PATIENT_UPDATE in REPORT TABLE with EXECUTE AN ACTION ON A SET OF ROWS

Referenced Table : PATIENT

Referenced Row : [_ThisRow].[Patient_ID]

Referenced Action : CALL_PATIENT_UPDATE

[Lupintam]

Hi, jyothis_m.

Thanks a lot for your response. 

---

@jyothis_m wrote:

Are you trying to change the column values in PATIENT table when it is changed in the REPORT. Say in REPORT FORM if Patient's age was having initial value as 30 from PATIENT TABLE and when user changes this value to 31 in REPORT FORM and save, you want the AGE Column in PATIENT Table to be updated to 31. Are you trying to achieve this? 

---

Thats is exactly what I want to do. 

I have been trying to do it by making two  similar actions:  one updates values of selected columns on patient's table (see 1st. image) and another action on Reports table witch select patient's record to be updated and call to the first action (see 2nd image).

This last action uses works fine and has the following configuration:

EXECUTE AN ACTION ON A SET OF ROWS

Referenced Table : PATIENT

Referenced Row :  SELECT(PATIENTS_TABLE[ID_Paciente],[_THISROW].[PATIENT_ID set for this row] = [ID_Paciente]) *

Referenced Action : CALL_PATIENT_UPDATE

* I have tried using your expression ([_ThisRow].[Patient_ID]) but it returns an error (see 3rd. image)

At this point, I think my problem is how to refer to the parent record ID which calls to the updating option. Updating process works fine if I change a fixed value in the Patient's record. Example: AGE = 35 but I don't know how to point or select the parent record (into Reports table) which contains the last updated data.  Initially, I have tried to retrieve the info from Reports table with LookUp expressions.

LOOKUP([_THISROW].ID_Informe, “Informes”, "ID_Informe", “Informe_Pte_Dirección_01”)

LOOKUP([_THISROW].Ref_Columm_of_Reports _Table, "Reports_Table", "Field_from_Reports_Table_to_obtain")

However, since this expression runs on Patient's table, I assuming that [_THISROW] doesn't point to any record of Report's table. Its quite clear but I didn't think on it until now.

Maybe a ChangeTimestamp column in the Reports table could help but Im still figuring out which expression could use.🤔

1st Image

2nd. Image

3rd Image

[jyothis_m · Accepted solution]

I have made a sample app for trying this. posting screenshots from it below. Its working fine.

[Lupintam]

Sorry about the delay. I'm following your indications right now and checking the INPUT() documentation again.

Once again, thanks a lot for you time and your advice.

[Lupintam]

Hi, jyothis_m.

Finally, works like a charm!

Thanks again for your time and effort!!

[jyothis_m]

---

@Lupintam wrote:

* I have tried using your expression ([_ThisRow].[Patient_ID]) but it returns an error (see 3rd. image)

---

Instead try this 

Filter("Patient",[ID]=[_ThisRow].[Patient_ID])