cdb_Update for numeric and calculated data

[sid]

Hi Guys

I have a question regarding cloud updates to records with NUMBERS  or Currency figures (well-all are numbers)in them:

for example , l'm doing stock control and I need to update 2 records , onHand and grossProfit (these are keys on the cloud database

so , as I understand it, you have to get the original key value for the above and store them into local variables and then do the calculation, for example, initially theres a 100 in onHand and $1000 in Gross profit. You sold 10 and made a buck on each thingummy you sold. so the calc is simple  and your updated values are 90 in onHand and $1010 in grossProfit.

Imagine that there are 2 users hitting the same record in the scenario, one of them is offline ..What happens then?
one guys sells the 10 , but the offline guy sells 50, so the correct values when the offline guy comes online and the cloud databases sync should be 40 in onHand and $1060 in grossProfit. It becomes more syncfusing if there are more people hitting that calculated key and a few of them are offline.

will it sync correctly, ie parse and do the calculations correctly ? (remember there are local variables involved) 


Correct me if this already exists , but ideally this should be a query operator update , meaning you minus 10 from the existing cloud value rather than overwriting the value .

cdb_updateValue(tTable,tRecordID,tKey,tOperator,tValue,tTarget) into tCalcEffect

cdb_updateValue("transactions",tRecordID,"onHand","-","10","cloud") into tCalcEffect

this be a very important feature. Does it exist? Or do you have to handle calculated syncs by writing code?

---

For anyone else reading this:

I forgot to say that the above command does not exit vis, cdb_updateValue.

I just used it as an example of how i think this should be handled, It will should also only work on numeric values for obvious reasons,

[mark_talluto]

Using the existing APIs would be destructive if multiple people were hitting the same value. The last person to write using cdb_update would win. Sync is a little different. Based on your params, the first or the last could win. Neither would be suitable for this scenario.

I think there are two ways we could solve this.

Improve cdb_update or make a new API for math operations
Store the updates as new records (like a ledger) and do the math when you want to know the result.

Both methods are useful to have. It depends on how you want to store your data in this scenario. We will toss #1 around in the studio and see what we come up with.

[sid]

Hi Mark

Thank you. It would be great if you could implement No1 . then let me know.

you see, if you create an operator and numeric based API it would solve this problem , This will only work on fields with numbers in them, so you would have to designate the field in LCM as such, much like you designate a BLOB field.

so the API will only be able to do a calculation (add or subtract) on the number in the field, for instance, add or subtract a number specified in the API from whatever the value is in the field

Basically this is the whole basis on any financial system, and why most systems fail horribly when it comes to correct qtyonhands. The sync sytems fail and thats why people are stuck with local client/server based systems

The solution is very simple , only allow addition and subtraction operators in a specifically designed API and only allow numbers in the field the API effects. Unfortunately most database systems don't have this, because programmers think the best solution is the most flexible one..and don't build in a restrictive API like this.

My suggestion is to leave cbd_update and create a new one called cdb_calcUpdate....or something

This way even if multiple people hit the record and the system just adds or subtracts to the total, It won't matter who wins the update.,the exception being if theres a divide or multiply operator in the calculation...which hardly ever happens except in reporting instances. The use for this sort of thing (just the add and subtract) is huge, for example, if you selling girl scout cookies and give each scout an app to track what they sold , and to subtract from the stock they have baked, this will be useful. Some may run out of data or whatever and will only connect once they get back home...so the data will still be correct.

I can think of a whole lot more examples and I hope you can fast track this feature.

The beauty of livecloud is the sync and the simplicity .

Let me know if you guys will do this soon, then I don't have to attempt to program in a complicated local 'Moves table' that basically will have to cron the calculations at the end of the day. Its a shit way to do it. A potential Fubar .snafu situation for sure.

Thanks for a great product and the instant feedback and help from your team. Its been a great journey for me with livecloud.

Sid

[sid]

Using the existing APIs would be destructive if multiple people were hitting the same value. The last person to write using cdb_update would win. Sync is a little different. Based on your params, the first or the last could win. Neither would be suitable for this scenario.

I think there are two ways we could solve this.

Improve cdb_update or make a new API for math operations
Store the updates as new records (like a ledger) and do the math when you want to know the result.

Both methods are useful to have. It depends on how you want to store your data in this scenario. We will toss #1 around in the studio and see what we come up with.

Hi Mark, 

Will the API for math operations be available soon? I am at a point in the development where I need it.

This will be a great feature and save on unnecessary code.

A simple one that can update a key in a record by adding, subtracting, multiplying, or dividing the existing number/fraction in the key by the specified number and updating the key with the result will be awesome.

Sid

[mark_talluto]

Hi Sid,

I am in LCM and CanelaDB right now. I will see if I can whip this up.

[sid]

That will be really awesome.

Thanks Mark

[mark_talluto]

Hi Sid,

I wanted to update you on how this is working.

The APIs available are:
cdb_calcUpdate pDataA,pOperator,pTable,pRecordID,pTarget
cdb_calcBatchUpdate pDataA,pTarget

You will be able to batch as many calculations as you like on as many keys as you like.

Valid operators: "add,subtract,multiply,divide"

The new API is working on local data as of now. I will work on the cloud part tomorrow.

[sid]

Hi Mark

Thanks man, This is awesome news and will same me a lot of time

It also solves the problem with transaction data pretty neatly,

I will give this a go .

BTW there was a guy on the forum who asked about incrementally generated invoice numbers. This will be a great solution.

Ill post a message on there as well , but you can elaborate if you feel necessary

Sid

[mark_talluto]

I am thinking that the API should return the updated value automatically so you know the new total. I will add that to the API. This would be useful in this invoice scenario as well.

[mark_talluto]

Hi Sid,

Thank you for responding to Peter. That is so helpful when we are all sharing information.

I completed the cdb_calcUpdate() funtions. They work on local and cloud. They return the result of the calculation so you can reliably get the result without risking the integrity of the data with a read.

I still need to create documentation for the API and I need to update the regions with the changes. I'll work on those tomorrow.