Sharepoint column entry validation with another list's column

[Patgat]

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.

[#2]

@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.

[#3]

@Ashish_Kohale  

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).

:)

[#4]

@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.

[#5]

@Ashish_Kohale 

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?

[#6]

@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

[#7]

@RobElliott 

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.

[#8]

Apologize for fourth point, its PowerApps not the PowerAutomate.

[#9]

@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. 

[#10]

@Ashish_Kohale 

So I understand it is not possible with VALIDATION PANEL of the Column List. Thats'it?

[#11]

@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