Hello @Rick_Bo,
I could simply delete the “color” schema which currently is of “string” type then recreate it with “data object relationship” and re-enter the data manually, but if there is an easier way…)
An easier way would be to write a simple script that will fetch all the data from the main (CRUD) app data table and then update every single object with relation to Color and Sides tables.
Here’s a quick sketch of what such a script might look like (using JS), I haven’t tested it, but it should work.
Hope this gives you some idea.
const Backendless = require('backendless')
Backendless.initApp('you-app-id', 'you-api-key')
const ColorPointsMap = {
red : 1,
orange: 2,
blue : 3,
// ... so on
}
async function run() {
const MAX_PAGE_SIZE = 100
async function findAllRecords(tableName) {
const total = await Backendless.Data.of(tableName).getObjectCount()
const itemsList = []
const requests = []
let currentQuery = {
pageSize: MAX_PAGE_SIZE,
offset : 0
}
while (currentQuery.offset < total) {
requests.push(Backendless.Data.of(tableName).find(currentQuery))
currentQuery = {
...currentQuery,
offset: currentQuery.offset + MAX_PAGE_SIZE
}
}
const results = await Promise.all(requests)
results.forEach(items => {
items.forEach(item => {
itemsList.push(item)
})
})
return itemsList
}
const records = await findAllRecords('Records')
await Promise.all(records.map(async record => {
await updateColor(record)
await updateSides(record)
await countPoints(record)
}))
}
async function updateColor(record) {
let existingColor = await Backendless.Data.of('Color').findFirst({
where: `color = '${ record.color }'`
})
if (!existingColor) {
existingColor = await Backendless.Data.of('Color').save({
color : record.color,
pointsTotal: ColorPointsMap[record.color]
})
}
await Backendless.Data.of('Records').setRelation(record, 'color', [existingColor])
}
async function updateSides(record) {
let existingSideRecord = await Backendless.Data.of('Sides').findFirst({
where: `side = '${ record.side }'`
})
if (!existingSideRecord) {
existingSideRecord = await Backendless.Data.of('Sides').save({
side : record.side,
multiplier: record.side === 8 ? 1.5 : 1
})
}
await Backendless.Data.of('Records').setRelation(record, 'side', [existingSideRecord])
}
async function countPoints(record) {
const data = await Backendless.Data.of('Records').findFirst({
where : `objectId = '${ record.objectId }'`,
properties: ['color.pointsTotal as colorPoints', 'side.multiplier as sideMultiplier']
})
await Backendless.Data.of('Records').save({
objectId: record.objectId,
points : data.colorPoints * data.sideMultiplier
})
}
run()
going forward, is there a way to calculate point totals per unit time as the kids enter data?
You can create an afterCreate Event Handler and do the logic there. Basically, you’ll need to find objects from the Color and Sides tables which are corresponding to what kids are entered (by using whereClause, see code above). And then update the record object with calculated points.
And retrieve that data to display their respective scores on the UI?
You may store totalPoints directly in the main CRUD table (I called it Records in my script), also you may count it on client-side retrieving only needed information about tit:
const data = await Backendless.Data.of('Records').find({
properties: ['*', 'color.pointsTotal as colorPoints', 'side.multiplier as sideMultiplier']
})
where side column is 1:1 relation to the Sides table and color is a 1:1 relation to the Color table.
If you have any questions, feel free to ask me here.
Cheers,
Stanislaw
