Help with expression for Action

I have a table called Operations_Master_Check which records [Product Type] and [Description] among others. I have another table called Product_Master_File which feeds those 2 columns in Operation_Master_Check. The product itself is a wood pole that goes through various stages. There are times a pole will be altered for quality reasons and what I have presented to the user is a serious of questions if they flag the pole as reject, the last options being [New Product Type] and [New Poduct Description] which are drop downs fed from the Product_Master_File.

What I want to do after the user selects the new type and description and saves the form is to trigger an action to update the Product_Master_File with the new values so the next time the pole is scanned it presents the user with the new data.

Based on the table and columns above could anyone help with an expression for that action?

Thank you.

Does the Operations_Master_Check row include a Ref to the corresponding Product_Master_File row? If so, what column contains that Ref value? If not, how can the app identify that corresponding Product_Master_File? For instance, perhaps both share a scanned barcode value?

Hi @Steve. Both tables share the scanned QR Code value. I am not using Ref. For all the values pulled from Product_Master_File into Operations_Master_Check I am using a Select expression. For the [New Product Type] and [New Description] fields I am just using a Valid_If expression to create dependent drop downs. Should I use Ref instead of Select to pull the values from the Product_Master_File table?

Nope, what you’re doing is fine.

This would require that the “new” values be existing values: the user would have to select an existing value form the dropdown menu and would not have the choice to alter the selected text. Is that your intention?

Yes exactly. Whatever the product is changing to has to be listed in the Product_Master_File. The user cannot add new values.

1 Like

So to clarify my understanding, when a row in Operations_Master_Check is given a value for New Product Type and/or New Product Description, the row in Product_Master_File with the same QR Code value as the row in Operations_Master_Check should be updated with the new Product Type and/or Description values. Yes?

Exactly.

1) Create GetOMCType Action

Create the action for the Product_Master_File table that will fetch the New Product Type value from the Operations_Master_Check table and set the Product Type column value accordingly.

image

  • Action name: GetOMCType
  • For records of this table: Product_Master_File
  • Do this: Data: set the values of some columns
  • Set this column: Product Type
  • To this value:
    ANY(
      SELECT(
        Operations_Master_Check[New Product Type],
        AND(
          ISNOTBLANK([QR Code]),
          ([QR Code] = [_THISROW].[QR Code]),
          ISNOTBLANK([New Product Type])
        )
      )
    )
    
  • Prominence: Do not display
  • Only if this condition is true:
    ISNOTBLANK([QR Code])
    

2) Create GetOMCDescription Action

Create the action for the Product_Master_File table that will fetch the New Product Description value from the Operations_Master_Check table and set the Description column value accordingly.

  • Action name: GetOMCDescription
  • For records of this table: Product_Master_File
  • Do this: Data: set the values of some columns
  • Set this column: Description
  • To this value:
    ANY(
      SELECT(
        Operations_Master_Check[New Product Description],
        AND(
          ISNOTBLANK([QR Code]),
          ([QR Code] = [_THISROW].[QR Code]),
          ISNOTBLANK([New Product Description])
        )
      )
    )
    
  • Prominence: Do not display
  • Only if this condition is true:
    ISNOTBLANK([QR Code])
    

3) Create SetPMFType Action

Create an action for the Operations_Master_Check table that tells the corresponding row of the Product_Master_File table to set its Product Type column value from this row (using the GetOMCType action created above).

  • Action name: SetPMFType
  • For records of this table: Operations_Master_Check
  • Do this: Data execute an action on a set of rows
  • Referenced Table: Product_Master_File
  • Referenced Rows:
    FILTER(
      "Product_Master_File",
      AND(
        ISNOTBLANK([QR Code]),
        ([QR Code] = [_THISROW].[QR Code]),
        OR(
          ISBLANK([Product Type]),
          ([Product Type] <> [_THISROW].[New Product Type])
        )
      )
    )
    
  • Referenced Action: GetOMCType
  • Prominence: Do not display
  • Only if this condition is true:
    AND(
      ISNOTBLANK([New Product Type]),
      ISNOTBLANK([QR Code])
    )
    

4) Create SetPMFDescription Action

Create an action for the Operations_Master_Check table that tells the corresponding row of the Product_Master_File table to set its Description column value from this row (using the GetOMCDescription action created above).

  • Action name: SetPMFDescription
  • For records of this table: Operations_Master_Check
  • Do this: Data execute an action on a set of rows
  • Referenced Table: Product_Master_File
  • Referenced Rows:
    FILTER(
      "Product_Master_File",
      AND(
        ISNOTBLANK([QR Code]),
        ([QR Code] = [_THISROW].[QR Code]),
        OR(
          ISBLANK([Description]),
          ([Description] <> [_THISROW].[New Product Description])
        )
      )
    )
    
  • Referenced Action: GetOMCDescription
  • Prominence: Do not display
  • Only if this condition is true:
    AND(
      ISNOTBLANK([New Product Description]),
      ISNOTBLANK([QR Code])
    )
    

5) Create SetPMF Action

Create an action for the Operations_Master_Check table that will perform all the actions to update the PMF row from the OMC row.

  • Action name: SetPMF
  • For records of this table: Operations_Master_Check
  • Do this: Grouped: execute a sequence of actions
  • Actions:
    1. SetPMFType
    2. SetPMFDescription
  • Prominence: Do not display
  • Only if this condition is true: TRUE

6) Attach SetPMF to Form

Fourth, set the SetPMF action created above as the Form Saved event action for the Operations_Master_Check table form view.

7) Test & Troubleshoot

1 Like

@Steve thank you so much for providing such detail. I will implement and test.

1 Like

@Steve the initial test of this seems to have worked perfectly based on your suggestions. Thanks a million.

1 Like

Hi @Steve

You helped me with the above a little while back and I have hit a little snag with it that I am hoping you might be able to help with.

The scenario is

  1. I create a product in Product_Master_File ([Pole Code] = 1478741, [Product Type]=38kv, [Product Code]=315)
  2. I create a record for that product in Operations Master Check where I have the option to recategorise and set a [New Pole Type] and [New Pole Code]. If the [New Pole Type] remains the same i.e. 38kv and I change the [New Pole Code] to, say, 263 the action works fine.
  3. However if I change both the [Pole Type] to, say, 10kv and the [New Pole Code] to 263 I am getting the following error upon saving the form.

image

Any help on this would be great, thank you.

This is likely due to a Valid if expression for one of the columns being modified. Have you looked at them?

@Steve

Can I just ask if you mean the columns being modified in the Product_Master_File table?

I am just using the dependent dropdown functionality. The dropdown values in the Product_Master_File Form are fed from a Support_Product_Master File so my valid if expressions are Support_Product_Master_File[Pole Type] and Support_Product_Master_File[Pole Code].

Perhaps a SELECT() expression would be a better way to build the dropdowns and hopefully prevent this error from triggering?

The way I interpret the error dialog you posted is that an action tied to the form is attempting to set one or more values of a row in the other table, but cannot because the value being set in the other table (Pole Code, it appears) fails that column’s Valid if check.

Thank you @Steve

1 Like

Hi @Steve. I think I have worked out what the issue is here.The problem is with the SetPMFType Action which references the GetOMCType Action which in turn contains the expression
ANY(
SELECT(
Operations_Master_Check[New Pole Type] (+ an AND() condition).

BASED ON MY EXAMPLE ABOVE;
In a normal scenario where the Pole Type and New Pole Type values remain the same and only the Pole Code changes the action updates the Product Master File initially and then next time the Product Code is scanned and the form is saved the Operations Master Check table is updated with the New Product Type and New Pole Code values.

But If the user selects a new Pole Type, say 10kv (and it was originally 38kv), they would be given a dependent drop down list that would not include 315 i.e. a totally diffent list of pole codes.

So when the form is saved and the SETPMFType action gets executed which in turn tries to execute the GETOMCType Action, the action fails because it is interrogating the OMC Table for the Pole Type which is still 38kv (as it won’t be updated here until the next time the Product Code is scanned) and that pole type would not offer a Pole Code option of 263 if the form was being completed in a normal routine using the standard dependent drop down function.

Does this make any semblence of sense to you :smile: and if so is there any work around that I might be able to implement? Haven’t been able to think of anything as yet.

Thank you.

Ugh… Having a tough time wrapping my head around the problem. Could you try rephrasing it?

1 Like

Apologies @Steve. It is a tough one to describe. If I was to provide access and a list of steps to recreate the error would that help understand the issue a little better?

We can try that, sure. :slight_smile: