May 31 2020 10:47 AM
Hello
Is it possible to validate a A list column entry with another B list's column?
Here is an example.
A List has for entry => TASKNAME / BEGINNINGDATE / ENDINGDATE/
B List has for entry => GLOBALWORK / BEGINNINGDATE
At the entry of A List I want to check that A.BEGINNINGDATE is >= B.BEGINNINGDATE.
If it is not perhaps I could add with a LookUP column the B.BEGINNINGDATE to A.List but the user will have to choose the date at the B Entry, and it is not what I want as a control...
Need Help I think.
Jun 02 2020 05:09 AM
@Patgat : Yes possible.
1) Use Query method to Sort the column of List B BEGINNINGDATE with descending order so that latest date will show at the top and always take first item BEGINNINGDATE value on form load of List A.
2) Save this value in global variable.
3) At the validation function, compare this value with the BEGINNINGDATE column of List A.
Hope this helps !
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Jun 05 2020 10:42 AM
Thanks very much to you.
But where do I have to code this query on the list. (i'm sorry i'm just a beginner).
:)
Jun 05 2020 11:48 AM
@Patgat :
1) What do you mean by entry, is it the List A form ?
2) Are you working on SharePoint online or on-premise?
3) If it is On-premise then you can use REST API to achieve this.
4) In SharePoint online, PowerAutomate would be easier to get the result.
Jun 05 2020 12:23 PM
1) What do you mean by entry, is it the List A form ? => I changed my text underneath
Is it possible to validate a A list column entry with another B list's column?
Here is an example.
A List has for entry => TASKNAME / BEGINNINGDATE / ENDINGDATE/
B List has for entry => GLOBALWORK / BEGINNINGDATE
When somebody enter an element on A List, I want to check if A.BEGINNINGDATE is >= B.BEGINNINGDATE.
If it is not possible, perhaps I could add a LookUP column of the B.BEGINNINGDATE to A.List? But the end-user would have to Select a date in this new LooUP column, and it is not what I want neither...
2) Are you working on SharePoint online or on-premise? => Sharepoint Online
3) If it is On-premise then you can use REST API to achieve this.
4) In SharePoint online, PowerAutomate would be easier to get the result. =>Don't you think it looks a little bit complicated, don't you? Do you mean I have to throw a flow each time there is a new element entry in the A List? If yes, how could I refer the result of this flow in the Column Validation Panel?
Jun 06 2020 02:31 AM
@Patgat if you've got several items in List B with the same BeginningDate, for example:
Globalwork - BeginningDate
Project Andorra - 01/07/2020
Project Seychelles - 01/07/2020
Project Antigua - 15/08/2020
Project Mauritius - 20/07/2020
Project Bahamas - 01/07/2020
If a user enters 01/07/2020 into List A there are 3 items in List B which have that date, so which column will you use to do the comparison? Is the Globalwork column also in List A? You need some way for it to do the comparison and at the moment I don't think you've got that because there is nothing linking the columns together. So with the current structure of columns I don't think the validation can be done.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
Jun 06 2020 02:49 AM
Hello Rob
I'm gonna give you an other example
List A
ProjectTask BeginDate EndingDate
Andorra1 01/01/2020 01/02/2020
Andorra2 01/03/2020 01/04/2020
Seychelles1 01/03/2020 01/04/2020
List B
Project EndingDate
Andorra 01/05/2020
Seychelles 01/04/2020
When a new project task is added on List A I would like to check => EndingDate of the Project Task in List A is <= EndingDate of Project in list B.
Jun 06 2020 03:08 AM
Apologize for fourth point, its PowerApps not the PowerAutomate.
Jun 06 2020 03:12 AM
@PatGa1495 : Use power Apps where you can select two different data sources and once you gets the metadata from sources you can compare the date values.
Jun 06 2020 03:16 AM
So I understand it is not possible with VALIDATION PANEL of the Column List. Thats'it?
Jun 06 2020 03:18 AM - edited Jun 06 2020 09:34 AM
@PatGa1495 no it's not possible with the validation panel as it can't use lookup columns. I'll try to work up a customised Power Apps form for you asap.
Rob
Los Gallardos
Microsoft Power Automate Community Super User