Valid if set blocks the edit action of the row

[Denis_Raddaoui]

Hi,

i am creating an application for testing production machines.

Every machine has a list of  'Checks' that need to be done.

I have a machine table and a checks table (each control is related to a single machine).

The 'Tests' table allows to save each test have been made to a new machine and has got the reference to the 'Machine' table and the 'Controls' table.

The intention is to have an insertion form that has this behavior:

• When the user selects a machine, the app suggest him only the controls relating to the selected machine.
• When the test is saved, if the user reselects the machine in a new form the app suggest him the controls relating to the selected machine minus the test that has already been done.

To have this behavior I applied this expression to the valid if:

IN([_THIS],
SELECT(OPERAZIONICONTROLLO[IdOperazione],[Macchina]=[_THISROW].[Macchina]) -
SELECT(Collaudi[OperazioneControllo],[Commessa]=[_THISROW].[Commessa]))

The expressions works very well in new rows, but when i want to edit a test row it tells me that the input is invalid because a row with that type of check is already saved in the 'Tests' table.

So my question is:

How can I optimize this expression without invalidating the record edit? 

@Steve @Joseph_Seddik @Marc_Dillon @TeeSee1 @Aleksi @Koichi_Tsuji 

[Aurelien]

Hi @Denis_Raddaoui 

What about:

IN([_THIS],
SELECT(OPERAZIONICONTROLLO[IdOperazione],[Macchina]=[_THISROW].[Macchina]) -
SELECT(Collaudi[OperazioneControllo],[Commessa]=[_THISROW].[Commessa])
-LIST([_THISROW])
)

[Denis_Raddaoui]

Hi Aurelian,

i tried the suggested expression but when i click Next the app still tells me the entry is invalid.

[Aurelien]

My bad. Should have been -LIST([_THIS]) instead !

IN([_THIS],
SELECT(OPERAZIONICONTROLLO[IdOperazione],[Macchina]=[_THISROW].[Macchina]) -
SELECT(Collaudi[OperazioneControllo],[Commessa]=[_THISROW].[Commessa])-
LIST([_THIS])
)

[Denis_Raddaoui]

Hi Aurelien,

i tried the expression with [_THIS] but it still tells me the entry is invalid (only in edit mode).

[Aurelien]

Can you tell what is the table name and column name where is applied this valid_if expression ?

[Denis_Raddaoui]

Name of the table: Collaudi

Name of column: OperazioneControllo (it's a ref column to the table OperazioniControllo)

[Denis_Raddaoui]

Can anyone kindly let me know if there is a solution to this topic?

Thank you very much for your support!

@Steve @Joseph_Seddik @Marc_Dillon @TeeSee1 @Aleksi @alekseyb @Koichi_Tsuji 

[Steve · Accepted solution]

SELECT(
  OperazioneControllo[IdOperazione],
  ([_THISROW].[Macchina] = [Macchina])
)
- SELECT(
  Collaudi[OperazioneControllo],
  AND(
    ([_THISROW].[Commessa] = [Commessa]),
    ([_THISROW].[_ROWNUMBER] <> [_ROWNUMBER])
  )
)

[Denis_Raddaoui]

It works!! Thank you very much!!