Forum Discussion

HannahWright2's avatar
HannahWright2
Qrew Trainee
5 years ago

Using a Jinja Boolean Expression in Query Step of Pipeline

I have a table full of criteria and every time I create a new record in a different table I want to query to see what criteria the new record matches. I have 5 different fields that will be compared against the criteria which can be organized in many different ways. While it is possible for me to put in several different filters I feel it would be better to maintain if I were able to comment out things and edit the code.

For this reason, I am trying to use Jinja as a boolean expression in the query step to see if fields on the new record match fields on the criteria (queried table) which will then allow the pipeline to continue.

So my question is, is there a way for me to compare a field from the trigger step (new record created) to a field on the criteria table? I know with the low code query format it allows you to pull values from the trigger step over to the fields you want to change. So far I have manually been adding b.field_name into my code and wanted to make sure me manually referencing the table I'm querying in jinja is part of the functionality Quickbase has designed for.

A huge thank you to anyone who can help!

------------------------------
Hannah Wright
------------------------------
Pipelines
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    I can't offer a Jinja answer. Maybe someone else can.

    But if you describe your criteria table, it may be possible to change that table so that it has a Key field which reflects the combinations of criteria, and then have a checkbox field on the Criteria table lookup down to the details so the details know which criteria it meets with a simple checkbox, thus avoiding the need to Jinja.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
  • HannahWright2's avatar
    HannahWright2
    Qrew Trainee
    5 years ago
    Mark, I appreciate your feedback on this. Your strategy was certainly going to be my plan B if I could not resolve how to use the new Pipelines functionality. I opened a ticket with Quick Base and was able to meet with a member of the Pipelines Development team to discuss Jinja syntax and how to use it in this way. He did mention they were aiming to come out with more in-depth Quick Base specific documentation in the next two weeks. They also plan on adding access to a Jinja formula library much like they do for regular formula fields while editing your code.

    The major thing I needed to change in my strategy is that you cannot reference the fields you are querying in Jinja on the query step. You need to query for everything and then have a conditional statement based on all of the records queried. From there you can now use the Expression Boolean fields like I was wanting to. I do suggest splitting your Jinja into many different Expression Boolean fields if you're working on something similar to this because it gives you the ability to troubleshoot if your query isn't giving you the results you were expecting.

    I also needed to make a few adjustments to my syntax. Based on his feedback I have been able to successfully implement this design using Pipelines and Jinja. Using Jinja has allowed me to comment out the many different ways a record could match criteria.

    I hope this helps someone out there looking to do something similar!



    ------------------------------
    Hannah Wright
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      5 years ago
      Hannah,
      i appreciate the feedback and certainly knowing Jinja syntax is a valuable skill set so maybe you did it this way to prove to yourself it can be done in Jinja and to improve your skills.

      In this specific use case though, could you have simply built a Boolean field in the Quick Base table and filter in Pipeline where that was true?


      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      mark.shnier@gmail.com
      ------------------------------
      • HannahWright2's avatar
        HannahWright2
        Qrew Trainee
        5 years ago

        Hi Mark,

        For my specific purposes that would not have worked. Criteria don't have to be specific sometimes they could accept "ALL".

        For example:
        Job: RN vs Job: ALL
        One criteria might only apply to RN's but another criteria might apply to any job we place for. This specific or ALL scenario is one that applies to all of the different criteria types I would need to query for. This being said, I would not be able to create a formula field that listed all the possible ways a criteria could match a person and use that in the pipeline. I've attached a picture of the Query I mapped out before writing the Jinja. It was also important I know why a criteria matched the query which using Jinja in Pipelines allowed me to do since I could comment out the scenario that pipelines ran on.



        -Hannah Wright



        ------------------------------
        Hannah Wright
        ------------------------------