Por los momentos en dev la tengo en gSheet, pero luego la migraré a mysql, en mysql alli voy a implementar con algun proceso esta funcion, y en gSheet lo estoy haciendo con GAS y con bot que llame esa función
ya intenté a puro bot pero no logré hacer que funcione, siempre hay salto o numeros repetidos
Entonces la solución es un trigger en la dB, que al momento de crear un nuevo registro de factura, busque el consecutivo máximo y asigne el siguiente. Y en el caso de GSheets lo resuelvo a nivel hoja, con una fórmula que va asignando el consecutivo siguiente, pero al ser una fórmula obviamente ralientiza el performance conforme se incrementa el número de registros
Si en mysql seria con algun proceso o trigger, pero en gsheet no me gusta con formula porque no puedes editar ese campo, y tengo alguna configuracion que el cliente decide si usar numeracion automatica o usar su propia
Entonces el problema es que le das demasiada flexibilidad al usuario 
(joke)
Thanks
Si, es posible, pero tambien es mas auto ajustable
Pero si en la numeracion hay clientes que manejan manual, asi como hay otros por no decir la mayoria es automatico y dependientes del registro autorizado del gobierno y secuencial, si bien es antiguo ese sistema
Thanks to everyone who shared their thoughts on this.
Gracias a todos los que aportaron su comentarios respecto al tema.
Try changing bot formula to this instead and see if you still get the same problems:
MAX(SELECT(Ventas[numero_factura], TRUE)) + 1
At times it seems that introducing the ‘Select’ statement it tries to pull a ‘refreshed’ version of the data. I don’t know for sure though and I don’t have a good way of testing this principle.
One other way to attempt this would be to run an apps script as your first step after the ‘add’ event with the following function:
function getLastRecord() {
let appId = 'your-app-id';
let accessKey = 'your-access-key';
let tableName = 'Ventas';
let url = `https://api.appsheet.com/api/v2/apps/${appId}/tables/${tableName}/Action?applicationAccessKey=${accessKey}`;
let selector = `FILTER("Ventas", ([KEY_COLUMN] = MAXROW("Ventas", "numero_factura")))`;
let payload = {
'Action': 'Find',
'Properties': {
'Locale': 'en-US',
'Selector': selector
},
'Rows': []
}
let options = {'method': 'post', 'contentType': 'application/json', 'muteHttpExceptions': true, 'payload': JSON.stringify(payload)};
const lock = LockService.getScriptLock();
const success = lock.tryLock(10000);
if (success) {
try {
let request = UrlFetchApp.fetch(url, options);
let response = request.getContentText();
let incrementedNumber = Number(response[0].numero_factura) + 1;
return incrementedNumber;
} finally {
lock.releaseLock();
}
}
}
This quasi acts as a time lock from the same function executing again. On the appsheet side make sure you choose the return value option from the script and after the script step then your next step should set the value for the invoice number which is returned from your apps script function. Take in mind that this will slow down the add process depending on the value included for the time lock in the apps script function (10 seconds, i.e. 10000 milliseconds in the script). This is only a suggestion to try, I have never attempted this myself, so I’m making no guarantees.
Thank you
I already tried, but it doubles the numbers.
If you do not need to support offline mode (sounds like you do not) then the closest you can get to generating sequential numbers is by using a Google Script to hand out the numbers.
There are two issues which you may find problematic:
Because you have to call the Script through a Bot, it will take some time for the round-trip communication. It is usually 10-15 seconds but depending on connections could take longer.
You could run into a situation where the Script has given back a number but due to poor connections that number doesn’t make its way back to the app - i.e. it gets dropped. The app would have to request another but others may have already been given out. This would leave a gap in the sequence.
I tried using the bot to run it with a webhook to call this GAS, but I couldn’t.
I assume it’s with a webhook, right?
Yes i am working now using GAS to solved this
So this is not a webhook from Appsheet, it is the ‘Call a script’ task. But it still does run the Appsheet API, just via the script instead of the webhook task.
I’ve also tried it this way and nothing happened.
of course in the script I added my:
appId = ‘xxxxxxxx’;
accessKey = ‘xxxxxxxx’;
Thanks everyone for your collaboration.
I’ve already resolved it, using AI along with GAS as always.
If certain conditions are met, then it will generate the sequential number.
Can you post your solution in case others run into this issue?
Yes, it will be great if you post your solution with any caveats it may have.
Sure, I’m still working on it
Sure, I’m still working on it
Does anyone know why this is happening to me? The field resets afterwards, and it’s not a script problem, because I even tried it manually—after setting the record’s status, I manually assigned the invoice number, and it still gets cleared. But it only happens if there’s a step that follows, and I don’t have any formulas in the field—nothing in “Valid If,” nothing in “Initial Value,” nothing in “App Formula,” nothing anywhere.
Check this
