Home › Forums › Classic DFFS › CAML Query Assistance for vLookup
- This topic has 2 replies, 2 voices, and was last updated 5 years, 3 months ago by Alexander Bautz.
-
AuthorPosts
-
-
June 9, 2019 at 18:32 #25649
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 matchThis 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:
-
June 9, 2019 at 19:10 #25658
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 -
June 10, 2019 at 13:15 #25659
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
-
-
AuthorPosts
- You must be logged in to reply to this topic.