Can't erase all the data inserted by .CSV file, the problem is the key

[vramirez80]

Hi all, how are you?.

I have data coming from an access control and the person ID is used as the key of my table, that data comes repeated several times in a day because people can access the place multiple times.

When new data is added the preceding data stored is not erased automatically, because of that was made an action to bulk erase the existing data based on the key of the table (the ID) and was used the formula SELECT(Listado[ID], TRUE) in the action; the action works but it doesn’t erase all the records I suppose that is because the key is repeated, then Google Gemini advice me to use a composed key using the ID and what I call the state of the user (STATE_1,…,STATE_3) according to the time of the day the user enters the site.

The above procedure was done adding te datetime of the event but, I try to use the system like that then AppSheet says that the table doesn’t has physical key column and it can’t continue.

Currently I can’t add physical columns to the data because the data comes with columns precreated.

The app is being made to count the quantity of people in each state in a period of time.

What can I do to being able to erase all the date in only one execution of the erase_all action without using bots?

Thanks in advance for the help

[Steve]

Please restate your problem in your native language. Please also post a screenshot of the columns list for the table that includes the key column.

[vramirez80]

Aquí voy:

Tengo data que viene de un control de acceso, esa data está almacenada en un archivo .csv el cual estoy importanto a mi aplicación usando una acción para importar archivos .csv, hasta ahí todo va bien. La tabla que almacena los datos obtenidos del archivo es de la siguiente forma:

ahí tiene como clave el ID de la persona y como label el nombre.

La aplicación está hecha para determinar en cuál período de tiempo entró la persona al área “Mañana“, “Mediodía“ y “Noche“, esos períodos tienen una hora de inicio y una hora de finalización cada uno, con respecto a eso como las personas pueden entrar y salir del área en cualquiera de los períodos de tiempo y/o los tres períodos de tiempo incluidos entonces el ID puede repetirse en el mismo día, hasta ahí no hay problema.

Con el fin de almacenar data nueva en la base de datos pensé mi aplicación para borrar el contenido previamente subido y almacenado antes de subir nuevo contenido, para ello creé una acción de borrado masivo la cual utiliza la fórmula SELECT(Listado[ID de persona], TRUE) para seleccionar todos los ID y meterlos en una lista para el borrado. Una vez utilizada la acción de borrado masivo, esta me genera primero el listado con el SELECT pero cuando borra los registros supongo que como ya borró la fila correspondiente a un “ID de persona?” específico, entonces si este ID aparece nuevamente en el listado pues no lo toma en cuenta para ser borrado.

Mi problema actual radica en que no se cómo hacer para que se borren todos los registros incluso los repetidos.

En caso de que alguien pregunte, no es necesario tomar los duplicados y contarlos como uno solo por cada período de tiempo, ya el cliente me indicó que para ellos está bien tener el duplicado y contarlo adicionalmente.

El paso siguiente con este problema fue crear una columna virtual y crear una clave compuesta con el ID de usuario, el nombre y la fecha-hora del acceso para que appsheet la utilice como clave, pero appsheet me dice que no puede continuar porque la clave de la tabla no es física

Entonces, cómo puedo resolver el problema del borrado incompleto?

Gracias de antemano por la ayuda brindada.

[Steve]

It sounds like your app is somehow getting rows with duplicate keys, and the duplicate keys are interfering with and even preventing your process of clearing the table.

You say Gemini advised you to try creating a composite key, but you encountered problems with that.

It might be helpful to know what the purpose of this table is, and what the CSV data is describing. My guess is the CSV data is a record of all check-ins from a different app, and the app you’re importing it into is intended to produce a report about the check-ins.

Is there a particular reason you’re using the person ID as the key?

A very simple solution to your problem would be to add a new column to the table to contain a new, independent row ID and use it as the key column instead. This row ID would be given a value by its App formula expression (something as simple as UNIQUEID() would be just fine). It would not get a value from the CSV data.

[vramirez80]

Yes , the problem is exactly as you described, I taught before about adding the table with the uniqueid() but the problem continues like follows: I’m making this app for a client who will upload the file by herself on a regular basis, so I can’t ask the client to add the column to the .CSV file before uploading it, the process for her should be as simple as possible, so the reason because I can’t create the column for the uniqueid() is that.

[Steve]

Your client doesn’t need to add any columns. You add a column to the app’s table. Or is this a Dropbox CSV file-based table?

[vramirez80]

It is a .CSV file created by de access control system and it comes preformatted from the source with all the columns previously created, because of that I can’t add another column to the file

[Steve]

You can’t seem to grasp my suggestion, so we can’t move forward. I have nothing more to offer.

[vramirez80]

Look, I think I understand your suggestion, at least partially. I’ve added a virtual column (the only type of column that appsheet allows me to add in the app’s table inside appsheet) and in that column I added the uniqueid; by doing that when the app is saved, inmediatly appsheet raise an error that says it can not run the app because the app doesn´t has a phyisical key column (I made the virtual column to be the key column).

In the other hand I tell you how the .csv file is generated: the external app generates itself a record of the incoming people every time they checks in along the day, then I need to export the records and the external app has a tool to export the data, that data is exported as a .csv file with only the columns that the external app defined. Then I take that .csv file and I uplodad it to the appsheet app by using an action to import .csv files; if the step of adding the column to the app ‘s table is made manually here then there will be no problem for me if I do that to upload the file to the appsheet app, the problem comes is if I tell the client to add the column by themselves, that step should be transparent to her.

Or perhaps I don´t know how if there is another method to add a column to the app’s table. Can you tell me?

Again thanks in advance for the help

[vramirez80]

@Steve To make another test then I used the ROWNUMBER column as a key, then if the file es loaded all the data is erased but if the data is to be uploaded then appsheet doesn’t do it because the column _ROWNUMBER is not part of the file.

[vramirez80]

@Steve look, I really like to know what you was explaining me but in the mean time I tough and made a new analysis of the situation and what can I use as a key and I found the following: the “Hora“ column stores the datetime data of the checkin and because there is only one access control in the site, and in average only one person can checkin in a time period of about 5 seconds, then the probability of having a repaeated “Hora“ field is nearly impossible, so I made the “Hora“ field the key of the table and voila, now te system erases all the data from the app.

[Steve]

Please post a screenshot of the app table’s configuration. Also post a screenshot of the entire screen displaying the error message.

[Jose_Arteaga]

Thanks @Steve, if I understand this correctly there is no error message, the process stops after finding / deleting the first instance.

@vramirez80,

1. Using Actions and Bots for Conditional Deletion:

This method is suitable for deleting records based on specific conditions or user interaction.

• Create a “Delete This Row” action:

  In the table where records are to be deleted, create an action of type “Data: delete this row.”

• Create a “Delete All” or “Delete Filtered” action:

  Create another action of type “Data: execute an action on a set of rows.”

  • Reference Rows: Define the set of rows to be affected. This can be the entire table (e.g., TableName[KeyColumn]) or a filtered subset based on a condition (e.g., SELECT(TableName[KeyColumn], [Status]="Finished")).

  • Referenced Action: Select the “Delete This Row” action created in the first step.

• Optional: Use a Bot for Automation:

  For scheduled or event-driven deletions, create a bot in the Automation section.

  • Event: Define the event that triggers the bot (e.g., a scheduled time, a change in a specific column).

  • Process: Add a step to run a data action and select the “Delete All” or “Delete Filtered” action created earlier.

Let me know if this heps!
Jose Federico Arteaga

[Steve]

@Jose_Arteaga

if I understand this correctly there is no error message, the process stops after finding / deleting the first instance.

You do not understand correctly:

@vramirez80

inmediatly appsheet raise an error that says it can not run the app because the app doesn´t has a phyisical key column

But now that you’ve interjected yourself, I’ll let you handle this.

[vramirez80]

Hey @Jose_Arteaga , thanks for the help, as I explain in a previous reply, the app is now working because I changed the key column to be the “Hora“ column what is your first advice, thanks again for the suggestion.

Reffering the use of a bot, that is a must not use directive in my app since the client doesn´t want to pay the cost of that.

[vramirez80]

@Steve and @Jose_Arteaga , thanks both for the help.

I show you the config that works right now

In the above table I changed the key to be the “Hora“ column, that change made my app to work correctly in the case of erasing the data, in this case the formula for the bulk erase action is

SELECT(Listado[Hora], TRUE, FALSE)

next comes the config of the app that doesn’t work

in the above configuration I used the virtual column (the only type of column that appsheet allows me to add without having a physical column in the data), I know that I can simply put the column name in my database (in this case a google sheet) but if I do that and after the schema is regenerated then when I try to upload the data appsheet raises a message, I don’t remember if as an error or in the UI of the app, but the message says that the file doesn’t contains the column that I added manually in the database. In the other hand, If I let the app with the config of the above image then when I try to upload the data appsheet raises a message in the app UI (not an error as I said before) that says it can not continue because the table doesn’t have a physical key column; for this case the formula written for the bulk erase action is

SELECT(Listado[Clave_completa], TRUE, FALSE)

following is the image of that

following is the action formula for the bulk erase with the wrong configuration

As both of you can see, I think my options was reduced after telling you this, luckily now with the “Hora“ column as key then the app works but I want to understand if possible to add somehow the column that I would need if I don’t use the “Hora“ column as key

[Jose_Arteaga]

I will switch to Spanish as I think it will be easier

La configuración con “Hora” como Key funciona correctamente. El cambio que quieres hacer es que cuando se carga el csv se actualiza una columna con determinados valores para formar una llave compuesta.

Automation bot:
Ir a Automatización > Bots en el editor de AppSheet.

1. Crear un nuevo bot.
2. Configurar el evento para que se active cuando se agreguen datos a la tabla que está importando.
   1. “Event source”: App
   2. Elegir la tabla
   3. Seleccionar el tipo de cambio a realizar
3. Agregar Step 1
4. Especificar “Run a Data Action”
5. Especificar Set row values
6. Set these column(s)
7. = [campo1] & “-” & [campo2]

La automatización se ejecutará después de agregar las filas desde el CSV.

Si estás importando a una tabla que ya contiene datos, revisa que la lógica de generación de claves tenga en cuenta posibles conflictos o actualizaciones según sea necesario.

Espero sea útil!

[vramirez80]

Gracias por tu aporte José pero no puedo usar bots en mi aplicación, y en específico, lo de la clave compuesta ya está resuelto también.

[tiko_ht]

Cuando cargas el CSV mediante Appsheet, este asigna automáticamente los UniqueID. Solo configura la tabla con un campo de este tipo y regenera, luego haz la prueba cargando uno de los registros del reloj checador

[vramirez80]

Hey, thank you all for your help, finally I have the app running, follows the outcome