Hi,
I’m wondering if there is a way to filter and then select the first match in a formula.
Something like: ItemPrices.filter(Price = ItemPrices.min(Price)).first().Vendor
Alternatively, something like: ItemPrices.sort(Price).first().Vendor
Thank you,
Hi @Aram_Zadikian,
I have a suggestion for you, based on what I am guessing you are trying to achieve.
Let’s assume you have entities called Items each of which is related (one-to-many) to Quotes.
A Quote has a numeric field called Price, and a Quote is related (one-to-one) to a Vendor entity.
Set up a formula in Items which looks for the lowest Price (returns a number):
Lowest_price: Quotes.Min(Price)
Now set up a formula in Quotes which checks to see if it is the cheapest (returns boolean):
Cheapest: Price == Item.Lowest_price
Now set up a formula in Items that finds the Quote whose Price matches the Cheapest (returns one (or maybe more) Quote):
Cheapest_quote: Quotes.Filter(Cheapest)
Finally, define up a lookup field in Items that finds the Vendor(s) associated with the Cheapest_quote, i.e.
Relation: Cheapest_quote
Field: Vendor
I know it sounds a bit long-winded, but actually, the helper formulas that you create along the way can actually be useful themselves in other ways.
For example, you might want a table filtered to only show the cheapest Quotes. Or you might want to calculate what percentage of the Quotes from a Vendor are the cheapest, etc.
Anyway, let me know what you think, especially if I have totally misunderstood your objective…!
Thank you @Chr1sG! Worked beautifully and I’m so glad to see how robust Fibery is.
As you can probably tell, I’m toying with building a PO system. Do you have any thoughts on number of records where Fibery will degrade in performance?
I also noticed that changes to the quote price took ~5 seconds to propagate through the tree. I have no issue there, but wonder what happens if there are tens of thousands of records.
One other question while on this subject. Is there still a way to take a single item in the case where there are multiple matches? I still want to force a single vendor during the next step; say generating the Purchase Order.
Thanks again for the help.
Hi again @Aram_Zadikian,
It’s definitely possible to only return a single item, but it will depend on what you want to choose as the ‘tie-breaker’. So if two vendors are quoting at the same price, what rule do you want fibery to apply to pick the winner? Alphabetical order of vendor name? Most recent quote date? Quantity of items quoted for? Oldest vendor?
And whatever you choose (assuming a field exists somewhere with that data) you need to decide what happens if there is still a tie for first place…and so on.
With regards to the performance of fibery as the number of records increases, I think you would have to see if @mdubakov and the team have any comments.
I’m not a SW developer, but I suspect that the delay in the fields getting update may relate to the fact that it is a web application and not a native app, and reflects the limitations of the browser client - cloud server model. Presumably it’s a tradeoff between having fast updates and flooding the server with checks for updates. I can’t imagine that the speed of calculation for the underlying dataset is the bottleneck.
In production largest accounts have 20-30K of records without performance degradation. I think Fibery can handle 500K records without problems, but it is hard to say for sure.
Formulas calculations are not perfect and we will definitely make them faster in future.
Quick follow up comment: you might need to put an IsEmpty() check in some of the formulas used e.g. checking if a Quote is the cheapest:
Cheapest: (Price == Item.Lowest_price) and (IsEmpty(Price) == false)
In the long term, I imagine the fibery team will add features that support data validation, including defining required fields (e.g. so that a Quote cannot exist without a value in the Price field) which would make the IsEmpty check redundant.
Thank you @Chr1sG. I’m still unsure on how to select a single item. Basically I’m ok with a random selection, but I want a way to force a single pick during a tie. Something like .first().
Is there a way to do this? Otherwise I fear that there will be edge cases when even the tie breaker will tie.
Also, as a data type, I would imagine .filter() returns a collection. How does operating on this collection work if I want to lookup a field on the result of filter?
Thank you!
@mdubakov, sounds great. Long term, do you see Fibery moving to support larger data sets as accounts grow?
We don’t see huge technical challenges, we use Postgres to store the data and it is quite performant on huge data sets. So when time will come we will make it work for millions of records
Hi again @Aram_Zadikian,
One way of guaranteeing there is no tie is to choose one of the cheapest quotes based on the creation date of the entity.
Going back to my original suggestion, you can add formulas and change the lookup in order to find the Vendor for the oldest-created Quote amongst all the cheapest quotes:
Add a formula in Items which finds the oldest (cheapest) quote (returns a date time):
Oldest_cheap_quote_date: [Cheapest_quote].Min([Creation Date])
Then add a formula in Quotes to identify the ‘winning’ quote (returns boolean):
Winning: Item.Oldest_cheap_quote_date == [Creation Date]
Set up a formula in Items to select only the ‘winning’ quote:
Winning_quote: Quotes.Filter(Winning)
Finally, replace the lookup field with one that finds the Vendor that is associated with the Winning_quote:
Relation: Winning_quote
Field: Vendor
I hope that makes sense.
Oh, and in answer to the question about .Filter(), yes, it returns a collection, and you can pretty much do all the same things you would do if it was a direct relationship (including perform a lookup to a field of the entities in the collection)
Implemented already CHANGELOG: Oct 27 / Sort() and First() in formulas, Undelete Types and Fields, Delete Workspace, Pen
Epics.Sort(Name).First().Name
Companies.Sort([Creation Date], false).First().Contacts.Count()
Companies.Sort(Name).First().Country.Name