CAML Query Assistance for vLookup

Home Forums Classic DFFS CAML Query Assistance for vLookup

Viewing 2 reply threads
  • Author
    Posts
    • #25649
      Notrega
      Participant

        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 5 years, 3 months ago by Notrega.
        Attachments:
      • #25658
        Notrega
        Participant

          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

        • #25659
          Alexander Bautz
          Keymaster

            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

        Viewing 2 reply threads
        • You must be logged in to reply to this topic.