Formula to change state (Rule)

Get Help
1

Hello, I have big troubles to set up a simple stockpile monitoring. I haven’t been successful even with help and AI so I am kindly asking here.

Task: stockpile database
Objective: change state depending on amount of stock
Desired functionality:

  • I have stock items (A, B, C, …)
  • Each item has “Amount” field of how much stock I have
  • Each item has “Limit” field, of what is my low stock threshold
  • If amount > limit: set state to “in stock”
  • If amount > 0 but < limit: set state to “limited supply”
  • If amount = 0: set state to “out of stock”

My status:

  • I have a database with all fields
  • I have an Rule trigger for Amount change
  • But I am unable to write the script to change the states :frowning: I am unable to change state by formula even with the most simple examples, just don’t know how to target it at all.

Thank you if you can help me with this!

2

I would do this a little differently. I actually just made a mock up of this some days ago: Create workspace — Fibery — Fibery

It’s generally better practice to use entities and formulas over rules. This is because it gives more information about the data, when things happened, and faster reports.

So in this case, you would have three databases: Product, Product In, and Product Out. It then calculated the amount of product you have based on summing product in - product out.

And around getting the field to to change based on the stock, there are two options (one is more complex than the other, but I prefer it as it avoids automations)

Prerequisite:
What you might have realized is that you can’t use formulas in filters. So you need to set up the formula in the database itself. Something along the lines of: If(Amount > Limit, “In Stock”, If(Amount <= Limit and Amount != 0, “Limited Supply”, If(Amount >= 0, “Out of Stock”)))

If a simple text field is enough, you can stop here. But if you want to use this for grouping, you’ll need to transform this into a database + relation (not single select):

Then then you have two options, one using rules, the other using auto-linking.

Rules: Trigger on entity update on the new formula field explained above. When triggered, it should update the status by formula (for this to work, you must use a database and a one-to-many relation, and not a single select): Statuses.Filter(Name = [Step 1 Product].[Stock Status]).Sort().First()

This will query the Status database, and return the value that has the name = to the text in the formula field you made above.

Formula and Auto-linking: (the reason for the auto-linking is because we can not query a full database in formulas like you can in automations, so we need to have the data available as reference in the entity itself. We’re essentially relating all status’s to all products, so that we can use them in the formula). Make a new database called “Status” and add a formula field called “true” to both that and the product database. Add a many to many relation between both that is auto-linked based on the fields “true”. Now all will be linked to each other. Now you can add a formula that is the same as the rule: Statuses.Filter(Name = [Step 1 Product].[Stock Status]).Sort().First(). This saves automation usage, and is then not editable by the user.

I know this is a lot. Lmk if this makes sense. I tried explain a bit of the why as well, but I hope it’s not too overwhelming!!

3

Hello, thank you for your reply. I have in fact an another DB with in-out rows, from which I calculate the Amount (very similar to your example Space), I just did not mention it since I believed I is not relevant :slight_smile:

But I don’t understand your example - are you creating a new custom field with statuses? I wanted to use “workflow states” as built in Fibery for that.

Here is a screenshot what I have

screenshot (2)
screenshot (2)3276×1665 214 KB

4

Exactly! A formula field with the following:
If(Amount > Limit, “In Stock”, If(Amount <= Limit and Amount != 0, “Limited Supply”, If(Amount >= 0, “Out of Stock”)))

Then your automation is triggered when this text changes.

If you are set on using the Workflow field, you can also create three different automations, one that filter for when Stock Status formula = “In Stock”, and two others for the other two states.

However this is less scaleable than using a new database called “Status” and relating it to the product. It then act like a single select, but allows for more flexibility (like querying all entries and filtering by name)

5

OK I understand now, thank you for your solution. I would keep this as a workaround plan B :slight_smile:

I am still open to a solution if this can be done with Workflow states with a script without a helper middle formula field.

1 Like