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.
Solved! Go to Solution.
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.
ANY(
SELECT(
Operations_Master_Check[New Product Type],
AND(
ISNOTBLANK([QR Code]),
([QR Code] = [_THISROW].[QR Code]),
ISNOTBLANK([New Product Type])
)
)
)
ISNOTBLANK([QR Code])
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.
ANY(
SELECT(
Operations_Master_Check[New Product Description],
AND(
ISNOTBLANK([QR Code]),
([QR Code] = [_THISROW].[QR Code]),
ISNOTBLANK([New Product Description])
)
)
)
ISNOTBLANK([QR Code])
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).
FILTER(
"Product_Master_File",
AND(
ISNOTBLANK([QR Code]),
([QR Code] = [_THISROW].[QR Code]),
OR(
ISBLANK([Product Type]),
([Product Type] <> [_THISROW].[New Product Type])
)
)
)
AND(
ISNOTBLANK([New Product Type]),
ISNOTBLANK([QR Code])
)
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).
FILTER(
"Product_Master_File",
AND(
ISNOTBLANK([QR Code]),
([QR Code] = [_THISROW].[QR Code]),
OR(
ISBLANK([Description]),
([Description] <> [_THISROW].[New Product Description])
)
)
)
AND(
ISNOTBLANK([New Product Description]),
ISNOTBLANK([QR Code])
)
Create an action for the Operations_Master_Check table that will perform all the actions to update the PMF row from the OMC row.
TRUE
Fourth, set the SetPMF action created above as the Form Saved event action for the Operations_Master_Check table form view.
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.
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.
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.
ANY(
SELECT(
Operations_Master_Check[New Product Type],
AND(
ISNOTBLANK([QR Code]),
([QR Code] = [_THISROW].[QR Code]),
ISNOTBLANK([New Product Type])
)
)
)
ISNOTBLANK([QR Code])
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.
ANY(
SELECT(
Operations_Master_Check[New Product Description],
AND(
ISNOTBLANK([QR Code]),
([QR Code] = [_THISROW].[QR Code]),
ISNOTBLANK([New Product Description])
)
)
)
ISNOTBLANK([QR Code])
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).
FILTER(
"Product_Master_File",
AND(
ISNOTBLANK([QR Code]),
([QR Code] = [_THISROW].[QR Code]),
OR(
ISBLANK([Product Type]),
([Product Type] <> [_THISROW].[New Product Type])
)
)
)
AND(
ISNOTBLANK([New Product Type]),
ISNOTBLANK([QR Code])
)
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).
FILTER(
"Product_Master_File",
AND(
ISNOTBLANK([QR Code]),
([QR Code] = [_THISROW].[QR Code]),
OR(
ISBLANK([Description]),
([Description] <> [_THISROW].[New Product Description])
)
)
)
AND(
ISNOTBLANK([New Product Description]),
ISNOTBLANK([QR Code])
)
Create an action for the Operations_Master_Check table that will perform all the actions to update the PMF row from the OMC row.
TRUE
Fourth, set the SetPMF action created above as the Form Saved event action for the Operations_Master_Check table form view.
@Steve thank you so much for providing such detail. I will implement and test.
@Steve the initial test of this seems to have worked perfectly based on your suggestions. Thanks a million.
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
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?
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.
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.
Ughโฆ Having a tough time wrapping my head around the problem. Could you try rephrasing it?
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.
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
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.
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
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.
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?
@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.
So the OMC submission updates the PMF?
@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.
Why not just have the user enter the new values in Pole Type and Pole Code directly?
@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.
I COMPLETELY understand. Complex app development is difficult. It helps to have others to bounce ideas off of.
Thanks for that @Steve. Your help and advice on here for me is extremely valuable and really appreciated.
Thank you @Steve
User | Count |
---|---|
37 | |
31 | |
29 | |
22 | |
18 |