Preventing duplicate values unless another column value is chosen

[Chalky23456]

I'm hoping you can help. I need to add data validation to our app based upon two fields. SpanID and JobID.

A JOBId is created for a SPANId (a location basically).

JOBID is always unique and I have a data validation formula that prevents duplicate entries successfully.

We have another column in the sheet - IsPassed. This is a true/false that shows a JOBId has been through the whole work process and has been passed/closed.

Jobs can also be removed from the programme via a REMOVED column (true/false). the records are still retained in the database though.

I'm trying to allow the addition of a duplicate SpanId only where the following is true for records that already exist:

REMOVED = False AND IsPassed=True

OR

Removed=True

Any assistance welcome as so far I've drawn a blank with formulas I've tried.

Thanks

[Suvrutt_Gurjar]

Please try 

ISBLANK(
FILTER(
"Table Name",
AND([_THIS] = [SpanID],   OR( AND( NOT([REMOVED]), [Passed]), [REMOVED]))
)
- LIST([_THISROW])
)

Please be aware that such valid_if expressions can fail in a multi user app, if two or more users are trying to work with the same record.

Please take a look at the last part of the following article.

List expressions - AppSheet Help

[pbalerio]

So many questions!

• Can you aim for the same functionality by using slice with the condition

OR ( [Removed],
          AND ( [IsPassed], NOT([Removed]) )

• Do you ever get a row removed that was not IsPassed? This leads me to another question:
• Why those many columns? Could IsPassed and Removed be singled out to Status Column {Projected, InProgress, Passed, Removed, ... )

2.14.0.0

[Suvrutt_Gurjar]

The answer in general to all the questions is "Yes"

However slice in itself will not prevent duplicates but it could be helpful in reducing the records being filtered. So instead of the filter running on all the records of the table, the filter runs on a reduced set of records.

Edit:

@pbalerio : I mistook you as the one who had asked original question. I thought @Chalky23456  had asked follow up questions. Sorry about that even though my response is valid for the questions you have asked @Chalky23456 

[Chalky23456]

Hi,

thanks for the response. I'll try to answer your questions.

Yes, a JobId could be removed from the programme without passing through the process (so it could have a false value for IsPassed). A SpanId can also have multiple entries(as we could work on that site every year - each would be a unique JobId though)

My understanding is that a slice is only for reducing the amount of data viewed/synced rather than for controlling what data can be added.

The reason for IsPassed being a yes/no is that it is a direct feed from a question in another app (this data is automatically passed within the database View itself rather than in Appsheets).

I don't have a lot of control over the columns/fields as they are controlled elsewhere by our Database department and I can only work within the Appsheets builder itself.

I've tried the listed formula and it just states that the span id already exists, 

Extract from the data source (SQL) below. I've removed loads of other columns to just show the Removed.

CutProgrammeId	JobId	SpanId	Removed
30	20030	82	1

This is the current formula, I've tried various different combinations of true and false but all seem to produce the same result. It also blocks entry where the IsPassed column is true.

ISBLANK(
FILTER(
"ITEAMNGVVWCUTPROGRAMMES",
AND([_THIS] = [SpanID],
OR([IsPassed],
[REMOVED]))
)
- LIST([_THISROW])
)

Very vexing.