Query or Vlookup similar method

Hi community,

I have 2 Types as follows

Fund Information Type with 2 fields

  • Ticker
  • Fund Name

Fund Metadata Type with 3 fields

  • Ticker
  • Type of Metadata
  • Meta

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?

Thanks

I think you are doing it ‘the proper way’ :slight_smile:
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,",")

2 Likes

Yeah. I tried to combine field like what you mentionned. But it doesn’t work.

Fibery doesn’t allow to finish the formula with the error

Sorry, can’t convert the Fund Metadata formula to Text — please create a new formula instead.

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.

2 Likes

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 :smiley:

Glad it worked :slightly_smiling_face:
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 :man_shrugging:

1 Like

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 :slight_smile:

  1. 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.
  2. Once we add First() function (:crossed_fingers: 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.

Investment Fund Wiki

→ Well I’m glad to learn more about this new function in August then :smiley:

Thanks for your time

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.

1 Like

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.

1 Like