Hi,
I’m experimenting with creating a game, where an avatar can equip items - and this is supposed to calculate total value.
Problem: the information I have is spaced across 3 tables, 1 master table that contains the Item ID (“primary key”, I guess?), and 2 tables with secondary key. I’m a little unclear on how to use the bubble operators to achieve this total calculation - mucked around trying to filter the lists but can’t find something that says “Item relates to current user”.
Appreciate any guidance - my need is best summed up in the visual below.
Bubble database operates a bit differently than other database’s and it doesn’t use Primary Key or Secondary Keys.
In bubble a Primary Key is the unique ID of the data entry, which is automatically created by Bubble for any data type entry created, and it can not be altered in any way.
A secondary Key in Bubble is actually just a related data type as a field. For example, if you have a data type of Product and Store, on the Product data type you can have a field that is of type Store, to related the store to the product. Sometimes, it may be advantageous to just store the unique ID of the related item or actually use the related data type.
For your issue of calculate total attack for Jane.
- Do a search for Items with constraints user = Jane. Is equipped=yes
- each Items Item StatName=Attack
- each Items StatValue:Sum
Thank you for the guidance and for studying my challenge. I have indeed set things up such that the “Item” column in the PlayerItemsInstanced and ItemStatModifiers link back to the Item table.
That said, I’ve achieved Step 1 (Search for PlayerItemsInstanced, Player = This User, IsEquipped = Yes).
I’m having trouble achieving Step 2 of your suggestion: “each Item’s Item” only gives me options pertaining to the main Items table (e.g., ItemDescription/ItemImage) - I can’t ‘crossover’ to the ItemStatModifiers table. Would you have any thoughts?
If PlayerItemsInstanced actually has a related field of Item it should work…show screen shots of your data in Bubble
Sure, table structure and sample data as per below:
Sample data:
Also deliberating whether I should just transfer the columns from Items to PlayerItemsInstanced - someone gave me guidance on keeping them separate but somehow it feels like it just adds unnecessary complexity.
I think it is fine as is…if not a big deal to do, the best alteration is to just put an is_equipped field onto itemstatmodifiers, then it is very straightforward as Search for ItemStatModifiers is_equipped = yes and StatName=Attack each items StatValue:sum, and if you wanted to constrain by Item type you could do that too.
With current structure:
Calculated Total Attack Value after equipping an item
Create Search for ItemStatModifiers with cosntraint StatName=Attack and filter by with constraint Item is in Search for PlayerItemsInstanced constraint is_equipped=yes: each items StatValue:Sum
If it was me, I’d alter the data structure to add IsEquipped to ItemStatModifiers
Thanks much for the guidance - I seem to still be getting an error though 
the filter is a constraint of Item: the operator of the constraint is ‘is in’ the value of the constraint is Search for PlayerItemsInstanced with constraint of is_equpped=yes each items Item and then you will add the each items StatValue:Sum
If you can not put into the constraint to generate the list of PlayerItemsInstanceds, then put that into a RG somewhere on page as you need to have the list of the PlayerItemsInstanced to compare.
This is more complex than needs to be and harder to implement which is part of my reason for suggesting other method of data structure alteration…it will also cost more than if you just alter data structure.
Thanks - I decided to simplify by combining Items with PlayerItemsInstanced. Regarding putting IsEquipped into the StatModifier table, I decided against it in the end as 1 item can occupy multiple rows in the StatModifier (e.g., a hat adding both Attack and Defense) - which means any equip/unequip would need to modify multiple rows at once.
I also realized that order of adding constraint matters (obvious after the fact!) - if I filtered by StatName = Attack first, the query fails. But if I filtered by Item is in Search for PlayerItemsInstanced first, the query succeeds.
I am now able to get the calculations correct. Thank you for the guidance and for engaging with this long question!
1 Like
