Help with expression for Action

[MauriceWhelan]

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.

[Steve]

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?

[MauriceWhelan]

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?

[Steve]

MauriceWhelan:

Should I use Ref instead of Select to pull the values from the Product_Master_File table?

Nope, what you’re doing is fine.

[Steve]

MauriceWhelan:

For the [New Product Type] and [New Description] fields I am just using a Valid_If expression to create dependent drop downs.

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?

[MauriceWhelan]

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

[Steve]

MauriceWhelan:

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.

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?

[MauriceWhelan]

Exactly.

[Steve · Accepted solution]

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.

• 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

[MauriceWhelan]

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

[MauriceWhelan]

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

[MauriceWhelan]

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.

Any help on this would be great, thank you.

[Steve]

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

[MauriceWhelan]

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

[Steve]

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.

[MauriceWhelan]

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

[Steve]

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

[MauriceWhelan]

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?

[Steve]

We can try that, sure.

[MauriceWhelan]

Hi @Steve. Thank you for this. The steps for recreating the error are below. Apologies about the longer sync time. I am still trying to put things in place to minimise this. Struggling a little because I can’t filter out much of the data in the Product Master File and Operations Master Check tables

1. Select ‘Administration’ from the bottom menu
2. Select 'ESB Aministration > ESB Product Administration > Create ESB Product
3. Enter a random Product Code
4. Select a Supplier from the list
5. Change Pole Type to 38kv
6. Change Pole Code to 315
7. Click Save (you should be directed to Fabrication Create Stage Form)
8. Enter value for Moisture Content
9. Change Recategorise / Reject to Yes
10. Recategorise Type: Cutback
11. Fabrication Reject Reason: Machine Damage
12. Fabrication Affected Area: Head
13. New Pole Type: 10kv
14. New Pole Code: 263
    15: Click Save

The error should trigger now (although it appears to submit both records to the Product Master File and Operations Master Check Tables respectively but only the original product details and not the new.)

I will provide access to the App (As Co-Author) & 2 Google Sheets also. If anything else is required please let me know.

Thanks again.

[MauriceWhelan]

Hi @Steve. I have had to make some change in my app to help with syncing so the steps above have changed a little. The steps are now

1. Select Fabrication Create
2. Enter a random Product Code
3. Select a Supplier from the list
4. Select Species & then Customer
5. Pole Type: 10kv
6. Pole Code: 263
7. Enter value for Moisture Content
8. Change Recategorise / Reject to Yes
9. Recategorise Type: Cutback
10. Fabrication Reject Reason: Machine Damage
11. Fabrication Affected Area: Head
12. New Pole Type: 10kv
13. New Pole Code: 219
    14: Click Save

Previously I had been creating the product in Product Master File and then creating an operational record for that product in the Operations Master Check which is what your suggestions at the beginning of the thread are based on. However I have changed it so everything is done in the Operations Master Check table and I made what I thought were the necessary changes to your suggestions above re GetOMCType, SetPMFType etc…

However I still appear to be getting a very similar error.

Thanks again.

[Steve]

In the action, GetOMCDescription, the Moisture Content column is set to the result of the expression. [_THISROW].[Moisture Content], which is setting the column to its own current value. Is that the intention?

[MauriceWhelan]

@Steve I added that in because I was getting an error saying (something like) 'Action Failed: Moisture Content would be blank and required. I tried that expression to use the value of Moisture Content in the current record.

My overall intention now is if [New Pole Type] and [New Pole Code] are given a value in the Operations Master Check table that they will update [Pole Type] and [Pole Code] as part of that record submission.

Your original suggestions worked perfectly when updating those values in the Product Master File so I am hoping to implement the same behaviour in the Operations Master Check table now instead.

[Steve]

MauriceWhelan:

My overall intention now is if [New Pole Type] and [New Pole Code] are given a value in the Operations Master Check table that they will update [Pole Type] and [Pole Code] as part of that record submission.

So the OMC submission updates the PMF?

[MauriceWhelan]

@Steve In the event of [New Pole Type] and [New Pole Code] being given a value by the user I actually just need the values of [New Pole Type] and [New Pole Code] to update the values of [Pole Type] and [Pole code] in the Operations Master Check table when the form is submitted.

[Steve]

Why not just have the user enter the new values in Pole Type and Pole Code directly?

[MauriceWhelan]

@Steve: For some reason I convinced myself that my logic was the best way to help the operators of the app capture the data as we needed. I have just run through it again in my head and your suggestion will work perfectly fine too

I guess I couldn’t see the forest for the trees. It has been a long couple of weeks.

Thanks again.

[Steve]

MauriceWhelan:

I guess I couldn’t see the forest for the trees. It has been a long couple of weeks.

I COMPLETELY understand. Complex app development is difficult. It helps to have others to bounce ideas off of.

[MauriceWhelan]

Thanks for that @Steve. Your help and advice on here for me is extremely valuable and really appreciated.

[MauriceWhelan]

Thank you @Steve