CAML Query Assistance for vLookup

[Notrega]

I have ZERO experience with CAML… but this is the last piece to finish the Shift Report project that you helped so much with.

I need to use this in a vLookup, but either I get
Code 1 – Pulls ALL DateV comparison matches but does not filter on Employee
or
Code 2 – Pulls only the Employee needed but does not filter on the DateV match

This is Code 1 – Pulls ALL DateV comparison matches but does not filter on Employee

<Where>
<And>
     <Leq>
        <FieldRef Name='StartDateV_x0023_'  />
        <Value Type='Number'>[currentItem:ShiftDateV_x0023_]</Value>
     </Leq>
<Geq>
        <FieldRef Name='EndDateV_x0023_'  />
        <Value Type='Number'>[currentItem:ShiftDateV_x0023_]</Value>
     </Geq>
     </And>
<And>
<Eq>
        <FieldRef Name='Employee_x002a_0'  />
        <Value Type='Text'>[currentItem:Staff_x0020_Name]</Value>
     </Eq>
    </And>
</Where>

This is the Code 2 – Pulls only the Employee needed but does not filter on the DateV match

     <Eq>
                <FieldRef Name='Employee_x002a_0' />
                <Value Type='Text'>[currentItem:Staff_x0020_Name]</Value>
     </Eq>
  <And>
     <Leq>
        <FieldRef Name='StartDateV_x0023_'  />
        <Value Type='Number'>[currentItem:ShiftDateV_x0023_]</Value>
     </Leq>
 
     <Geq>
        <FieldRef Name='EndDateV_x0023_'  />
        <Value Type='Number'>[currentItem:ShiftDateV_x0023_]</Value>
     </Geq>
  </And>
</Where>

• This topic was modified 4 years, 10 months ago by Notrega.

Attachments:

1. 

   Code1.png
   

2. 

   Code2.png
   

[Notrega]

Forgot to add – the DateV field is the day# of the year… 6/9/2019 = 160
The DateV is meant to check to see if the ShiftDateV falls between the Start/End DateV

[Alexander Bautz]

Thanks for feeding my piggy bank!

To check if a date falls between two dates you must use a CAML like this – using an ISO 8601 date format (Please note that both the tags are in the top):

<Where>
    <And>
    <And>
        <Leq>
            <FieldRef Name='StartDateV_x0023_' />
            <Value IncludeTimeValue="False" Type='DateTime'>2019-01-01</Value>
        </Leq>
        <Geq>
            <FieldRef Name='EndDateV_x0023_' />
            <Value IncludeTimeValue="False" Type='DateTime'>2019-01-01</Value>
        </Geq>
    </And>
        <Eq>
            <FieldRef Name='Employee_x002a_0' />
            <Value Type='Text'>[currentItem:Staff_x0020_Name]</Value>
        </Eq>
    </And>
</Where>

This mean you must get your “ShiftDateV_x0023_” field to hold a date in the above format: 2019-01-01 if you want to use it like [currentItem:ShiftDateV_x0023_] in your CAML.

Alexander

[Author]

Posts