Discussions

 View Only
Expand all | Collapse all

Pipeline code using 'or' and 'and'

  • 1.  Pipeline code using 'or' and 'and'

    Posted 02-07-2023 09:34
    We need to populate a 'month' field if family member 1 or family member 2 has a birthday that month. Each month has its own field. In this example, I am using the MAY TOUCH field. So, if (family member 1 or family member 2 has a May birthday) and MAY TOUCH is blank, add the value 'Birthday Month' to MAY TOUCH. However, I get the red box around that field which means something isn't right. I have scoured this forum and can't seem to figure out what part of the code is wrong. I have tried many different words like isNull, ="", and using quotes around "Birthday Month", etc, but none of those work. Here is my code:

    {% if (a.account_number_family_1_birth_month_3_charac is May or a.account_number_family_2_birth_month_3_charac is May) and a.may_touch is not defined %}
    Birthday Month
    {% endif %}

    The second part of this will be....if MAY TOUCH is defined, then MAY TOUCH value will have 'Birthday Month' appended to the text already in the field.

    Step A in the Pipeline is Search Records where account_number_family_1_birth_month_3_charac does not equal 'blank' or account_number_family_2_birth_month_3_charac does not equal 'blank',
    step B is Update Record

    ------------------------------
    Amy Gosz
    ------------------------------


  • 2.  RE: Pipeline code using 'or' and 'and'

    Posted 02-07-2023 10:40
    Amy,

    I am not a Jinja guru.  @Prashant Maheshwari  @Doug Henning are the kings of this.   However I think your expression has the wrong operator and the text value needs quotes.

    a.account_number_family_1_birth_month_3_charac == 'May'



    ------------------------------
    Don Larson
    ------------------------------



  • 3.  RE: Pipeline code using 'or' and 'and'

    Posted 02-07-2023 10:48
    == is a comparison operator to compare two objects so I don't thnk it would be used in this case.

    ------------------------------
    Amy Gosz
    ------------------------------



  • 4.  RE: Pipeline code using 'or' and 'and'

    Posted 02-07-2023 12:33
    Amy,

    I looked at this again and may have a non Jinja solution for you.

    FID 111 is a Date
    FID 112 is a Date
    FID 113 is a Text 


    # Character Date Search
    #

    # Account slugs:
    # - quickbase[XXXX]: Pipeline Token <None>
    ---
    - META:
    name: Character Date Search
    - QUERY quickbase[XXXXX] record search -> a:
    inputs-meta:
    export_fields: '"Touch, OneCharacter, TwoCharacter" <113, 111, 112>'
    query: ({111.EX.'May'}OR{112.EX.'May'})AND{113.EX.''}
    table: '"Parents: Parent" <bp4igy3re>'
    - a<>LOOP:
    - DO:
    - a<>ACTION quickbase record update -> b:
    inputs:
    touch: May
    ...



    ------------------------------
    Don Larson
    ------------------------------



  • 5.  RE: Pipeline code using 'or' and 'and'

    Posted 02-07-2023 15:45
    All fields are text fields, the '3 charac' field is capturing the 3 letter abbreviation used for months. I am not querying or using date fields for this.

    ------------------------------
    Amy Gosz
    ------------------------------



  • 6.  RE: Pipeline code using 'or' and 'and'

    Posted 02-07-2023 16:20
    My apologies for typing without thinking.   In the App where I built the Pipeline I did use Text fields

      I tested this on multiple records.  If [Touch] is null and either [OneCharacter] or [TwoCharacter] has the value of "May",   then the [Touch] field is written with the value.





    ------------------------------
    Don Larson
    ------------------------------



  • 7.  RE: Pipeline code using 'or' and 'and'

    Posted 02-08-2023 13:04
    Edited by Prashant Maheshwari 02-08-2023 20:23

    • Are we implying there are 12 fields for each month Jan /Feb / March and whenever a family member has a birthday , that particular field will get updated each month ? 
    • All items are stored as text ? Can you give example of one field to gauge ? 
    • I keep thinking why won't a QuickBase formula achieve this?
    I did a simple test with 
    
    (text)Name - Prashant
    (date)DOB - 29/05/1985
    (text)Month
    
    pipeline Jinja
    {{(a.dob|timezone('Asia/Calcutta')).strftime('%b')}}
    
    The advantage of above method is simply referencing the date field and month will be found. Once we are clear with the structure , we could play around



    ------------------------------
    Prashant Maheshwari
    ------------------------------



  • 8.  RE: Pipeline code using 'or' and 'and'

    Posted 02-09-2023 08:17

    Yes, there is one field for each month. These fields are used as prompts for what type of contact may be needed. We do not capture full dates for the birthday and in many cases only know the month. If there is already text in the month field, then we don't want to overwrite that, only add on additional note about it being a birthday month. If there is no text, then we will just populate it noting that it is a birthday month.

    12 Month fields - text, multiple lines

    3 charac Month field - text

    Family Member 1 and 2 fields - text

    I am trying to figure out how the pipeline can check for values in the month field and if no value, populate with 'Birthday Month'. If there is a value, then keep current value and add 'Birthday Month'.



    ------------------------------
    Amy Gosz
    ------------------------------



  • 9.  RE: Pipeline code using 'or' and 'and'

    Posted 02-09-2023 09:43
    Edited by Doug Henning 02-09-2023 12:33

    Don's original reply is correct, you need to use == for the comparison and put quotes around the value to compare.  This will add to the may_touch field if a value exists, or set it to "Birthday Month" if empty.

    {%- if (a.account_number_family_1_birth_month_3_charac == "May" or a.account_number_family_2_birth_month_3_charac == "May") and not a.may_touch %}
    Birthday Month
    {% else %}
    {{ a.may_touch }}
    Birthday Month
    {%- endif %}



    ------------------------------
    Doug Henning
    ------------------------------



  • 10.  RE: Pipeline code using 'or' and 'and'

    Posted 02-09-2023 14:25

    Hi Doug...this did not work. For some reason it got added to every record, not just the ones with a May birthday. Also, the code didn't like the quotes around May so I removed those. I did try a couple other options, but it still isn't populating only when May is the birth month. The other thing for this is that if the 'month field' already has 'birthday' in it, then I don't want it to add it again. This will be a scheduled pipeline to run daily.



    ------------------------------
    Amy Gosz
    ------------------------------



  • 11.  RE: Pipeline code using 'or' and 'and'

    Posted 02-09-2023 16:14

    Hi Amy, can you post your code?  The quotes around May are absolutely required for checking equality of strings, so I'm guessing you have another error in your code.



    ------------------------------
    Doug Henning
    ------------------------------



  • 12.  RE: Pipeline code using 'or' and 'and'

    Posted 02-10-2023 08:51

    HI Doug...I copied your code, but did go back and shorten the birth month field names. Here is my code that is adding Birthday Month to every record. "May" is in red in the code. 

    {% if (a.familymem_1_birth_month_3_charac == "May" or a.familymem_2_birth_month_3_charac == "May") and not a.may_touch %}
    Birthday Month
    {% else %}
    {{ a.may_touch }}
    Birthday Month
    {% endif %}



    ------------------------------
    Amy Gosz
    ------------------------------



  • 13.  RE: Pipeline code using 'or' and 'and'

    Posted 02-10-2023 23:14

    Hi Amy, sorry my mistake on the above code!  Here's a version that fixes the message displaying for every month:

    {%- if a.may_touch %}
    {{ a.may_touch }}
    {%- endif -%}
    {%- if (a.familymem_1_birth_month_3_charac == "May" or a.familymem_2_birth_month_3_charac == "May")  %}
    Birthday Month
    {%- endif %}

    Also, can you post a screenshot of the error you're seeing in the Pipelines editor?  I am not seeing those errors for the quotes around "May":



    ------------------------------
    Doug Henning
    ------------------------------



  • 14.  RE: Pipeline code using 'or' and 'and'

    Posted 02-13-2023 09:06

    Hi Doug....this worked much better as it did only populate records with May for one or both of the family members. I ran it twice and it put birthday month on the record again. We would not want this to happen. I tried adding 'and a.may_touch does not contain "Birthday" but I get the red box around the field. I truly appreciate your help working through this code. As far as "May" being in red, then that is my misunderstanding thinking red meant there was an error.

    {%- if a.may_touch %}
    {{ a.may_touch }}
    {%- endif -%}
    {%- if (a.familymem_1_birth_month_3_charac == "May" or a.familymem_2_birth_month_3_charac == "May") and a.may_touch does not contain "Birthday" %}
    Birthday Month
    {%- endif %}



    ------------------------------
    Amy Gosz
    ------------------------------



  • 15.  RE: Pipeline code using 'or' and 'and'

    Posted 02-13-2023 09:53

    Hi Amy, glad that part is working!  You can add this to keep it from adding "Birthday" if it's already in the field:

    and "Birthday" not in a.may_touch

    Here's the updated code:

    {%- if a.may_touch %}
    {{ a.may_touch }}
    {%- endif -%}
    {%- if (a.familymem_1_birth_month_3_charac == "May" or a.familymem_2_birth_month_3_charac == "May") and "Birthday" not in a.may_touch %}
    Birthday Month
    {%- endif %}


    ------------------------------
    Doug Henning
    ------------------------------



  • 16.  RE: Pipeline code using 'or' and 'and'

    Posted 02-13-2023 11:29

    HI Doug....yes that worked much better by not adding again, but now it's not adding Birthday Month if the field is blank (no May Touch already populated). I am so sorry I am having so many issues with this, but writing this code is not very intuitive and I have figured out a lot of codes on my own. :(  Any further advice to correct that?



    ------------------------------
    Amy Gosz
    ------------------------------



  • 17.  RE: Pipeline code using 'or' and 'and'

    Posted 02-13-2023 15:28
    Edited by Doug Henning 02-13-2023 15:31

    No worries Amy, I think we're close to solving this!  I was doing my code testing in an online Jinja parser, but when I just tried it in Pipelines it got an error (...TypeError: argument of type 'NoneType' is not iterable) when run.  If that's what's happening for you, this should fix it:

    {%- if a.may_touch %}
    {{ a.may_touch }}
    {%- endif -%}
    {%- if (a.familymem_1_birth_month_3_charac == "May" or a.familymem_2_birth_month_3_charac == "May") and "Birthday" not in a.may_touch|string %}
    Birthday Month
    {%- endif %}

    The issue occurs when a.may_touch is not defined, it can't be compared to a string. The solution is to convert a.may_touch to a string by appending "|string" to it as seen in line 4 above.

    Hope that works for you!


    ------------------------------
    Doug Henning
    ------------------------------



  • 18.  RE: Pipeline code using 'or' and 'and'

    Posted 02-14-2023 09:44

    Doug....you are amazing! It works perfectly now. Users are going to be super excited! Thank you so much for sticking with me on this.



    ------------------------------
    Amy Gosz
    ------------------------------