Fund Information is related to Fund Metadata as 1:n
I want to create a new field in Fund Information Type that lookup the content of Meta field in Fund Metadata Type that satisfies 2 conditions:
Fund Information.Ticker is the same as Fund Metadata.Ticker
have a specific value in Type of Metadata field
A pseudo query of my needs
SELECT Meta
FROM Fund_Metadata
WHERE Fund_Metadata.Type_of_Metadata = "my_criteria"
AND Ticker = Fund_Metatadata.Ticker
Currently, Iâm doing a hacky way like this in Fibery.
I create a new formula-type field named Tmp in Fund Information Type
[Fund Metadata].Filter(([Type of Metadata].Name = âAuditorâ) and (Ticker = [Fund Information].Ticker))
Then I create another formula-type field in Fund Information Type, in order to retrieve the content from Meta field in Fund Metadata that satisfy my 2 conditions.
[Tmp].Join(Meta,â,â)
I want to ask if someone know a proper way to implement this task in Fibery?
I think you are doing it âthe proper wayâ
But thereâs actually no reason why you need to use two fields. You should be OK to combine the filter and join functions: [Fund Metadata].Filter(([Type of Metadata].Name = âAuditorâ) and (Ticker = [Fund Information].Ticker)).Join(Meta,",")
You canât change the return datatype of a formula once it has been created, so you will need to create a new formula field from scratch.
Your Tmp formula is returning a collection of âFund metadataâ entities, whereas the join formula is returning a text string.
Thanks again for you explanation.
Yes. It works by creating a new field from scratch and combining the formula.
Though I hope someday Fibery will have a more intuitive way to do query like this
Glad it worked
Not sure why you think this method is âhackyâ or not intuitive, but I guess intuition varies from person to person and depends heavily on previous experiences/tools used
Agree with your perspective. As my point of view, I expect that having a proper Query formula like in Google Sheet where you can do query with Select ... Where ... is much more intuitive than what I currently do in Fibery for an equivalent output of a query statement.
I wonder: do you expect the query to get precisely one Fund Meta or could there be many of them?
For this reply, Iâll assume itâs a single Entity that you are looking for
How is the Ticker related to Fund Information? If itâs 1:1, I think you could reorganize things to skip the extra Ticker = [Fund Information].Ticker filter.
Once we add First() function ( this August), the solution would be a single Formula: [Fund Metadata].Filter([Type of Metadata].Name = âAuditorâ).First().Meta.
Please feel to correct my assumption and comment on this approach.
Yes. I expect the query to get a single one Fund Metadata
â [Fund Information].Ticker related to [Fund Metadata].Ticker as 1:n.
Since I doubt that I explain correctly the entity relationship, you can find here my app. Tell me when you are done with investigation, so I stop sharing.
The Ticker field in Fund Metadata is a lookup of the Ticker field in Fund Information, so you definitely donât need the logic to check it.
Until the First() function is released, you should be fine with: [Fund Metadata].Filter([Type of Metadata].Name = âAuditorâ).Join(Metadata,"")
as long as only there is only one Metadata entity of the type Auditor for each Fund Information entity.
Thanks for taking time to investigate.
It works exactly like what you said (omitting the Ticker logic checking part)
If possible, could you please explain to me the underlying logic in Fibery how can this work?
In my understanding, I need to check 2 conditions, so the Filter formula should have 2 condition-logic-check, like what a SELECT query should do. But within Fibery, I need only 1 condition for Filter formula
The Ticker field in the Fund Metadata type is merely a lookup. This means that it is automatically calculated from the Ticker field of whichever Fund Information entity the Fund Metadata entity is associated with.
It will therefore always be the same value, so there is no need to check if they match.