Skip to main content
Question

Searching for text in field name within formula ie. 2019, 2018, 2017 in field name

  • July 28, 2019
  • 0 replies
  • 32 views
I am trying to make a formula that pulls through historical data to find which one to utilize.  Currently I'm using dates but I can see multiple areas where this is very useful.  

In excel it would be a "Match" function against the headings of the page.

I need to be able to search all the field titles and figure out which one to pull forward into a formula.  The way to do this without this match function is quite extensive and being able to search would make a lot less laborsome.


I appreciate any help you can provide.  I had searched through the information out there first but did not see any requests for this information.
    This topic has been closed for replies.

    There is no equivalent function but you can also do something like this,


    Case(Year([My Date Field]),

    2018, [Rate 2018],

    2019, [Rate 2018],

    2030, [Rate 2018])


    But that is not really a Quick Base way of doing things. There are better ways with having a table and then a look up field. Perhaps if you explain what you were trying to accomplish we can suggest a table structure that would do that more elegantly.
    • JeremyCohen2
    • Author
    • Registered
    • July 29, 2019
    Maintaining history of years prior while allowing the fluctuation of what field is used in the calculation.  

    In this circumstance it is important to keep historical data but the calculation should always be on the current year.  I have other circumstances that are similar where historical data is also important.
    So, a typical Quick base methodology would be to have a table of Years where the key field is Year, like 2019.  

    Then make a relationship down to the details table based on say a formula field like

    Year ([invoice Date])

    and lookup an fields from the Years Table that that you need, down to the detail records.



    • JeremyCohen2
    • Author
    • Registered
    • July 29, 2019
    So what your suggesting is a separate table for this information that ties to each year?

    How could i separate that by each record would it be more like...

    "Record Name", "Process Information", "Year it ties to" as 3 separate fields?  then wouldn't the unique ID end up becoming just as cumbersome?

    I created a nested if statement to include the variances which ended up quite astronomical just to include 3-4 years of data.  Was hoping for a way to keep it searching so that anyone in the future could add a new field for the new year and it would just automatically populate the data correctly.


    Would it work to have a case within the brackets...for instance "["&Year(today())&"]"

    This would involve something similar to the "indirect" formula within excel
    Either you would need to describe in more detail what you are trying to accomplish or else we would need to book a one on one session to get this working.

    ie please describe the business issue that you are trying to solve for without going into how you would do this in Excel.  Like say in works what you want to have happen automatically.


    Terms & ConditionsAccessibility statement