Hi.
I have a Scheduled_Maintenance_Library table which contains columns, among others, [Area], [Category], [SubCategory], and [Asset]. I have another table Work_Orders_Master which contains all tickets for Maintenance and most of these tickets are created from the data in the Scheduled_Maintenance_Library. Until now the user had to create these tickets manually by selecting from dependent dropdowns populated by Scheduled_Maintenance_Library i.e. [Area] etcโฆ
I want to create an โAutomate Open Ticketโ action and dynamically create an open ticket in the Work_Orders_Master table for every row in the Scheduled_Maintenance_Library but I am getting error โCould not set value because column Area would become blank and required.โ
I have attached a screenshot of my setup. Am I correct in my setup for my required result or do my expressions need to be a bit more dynamic?
Hi @MauriceWhelan Do you have other required columns in your Work_Orders_Master table ? You may need to include them/it
hI @Lynn. The only other required field is Timestamp but for some reason it is not in the list of columns to select from. Not sure why that is (I pressume this value is pass through automatically in the background). I do have a couple of RequiredIF formulas in place but I would not need to pass through values for those. Do I need to include columns that have a RequiredIF Formula?
See below
@Lynn @MultiTech_Visions . I have added in all required columns but for some reason I am still getting the attached screenshot for [Category] when firing the action.
Is there anything else I could maybe troubleshoot?
Hmmmโฆ Iโm assuming that each of the columns youโre setting in the action will actually have data.
Like, in the above screenshot we see the โCategoryโ field is failing because, it says, that Category is blank.
Is it?
โAny columns not defined will get their initial values or app formulas. If a required column is not specified and does not have an initial value, you will be shown an error in the editor.โ
The 2 tables have a bunch of corresponding columns [Area],[Category],[Sub Category], and [Asset]
Is my setup correct with [Area] in the formula field?
I have checked my Scheduled_Maintenance_Library table again and there is a [Category] value in for every row.
Everything looks set to me but I am obviously not doing everything correctly. Happy to share app and/or sheet with you Matt if that is ameniable?
Or if there is anything else you could suggest I should clarify in my setup.
sure, I can take a look; add multitechvisions@gmail.com as a co-author.
Thank you Matt. I have sent on the invite.
Okay I took a quick look, but everything looks fine to me.
@Aleksi could you take a look? Iโm wondering if thereโs a bug with the new action to add a row to a table? I thought the explanation of how this mechanism works is that all the edits are treated as a user edit then a form save, right?
I checked that all columns will indeed have a value, but I noticed thereโs some automated dependent drop-down action going on.
Iโm wondering if the dependent dropdown automation is getting in the way of how the new record is being edited in the background???
@MauriceWhelan is that okay???
App: PDMAssetManagement-813993-19-08-08
Absolutely no problem. Just let me know if you need anything else from me.
Matt can I ask another separate query on this new feature. Is there a way that I can trigger this โAutomate Open Ticketโ based on a date similar to the Reports facility.
Each row in the Scheduled_Maintenance_Library table has a [Start Date] and if possible I would like to use this action to automate an open ticket in the Work_Orders_Master table on that date.
Could I take a look if Aleksi hasnโt already?
Sure @Steve Do you need me to share with your email or is the App ID above enough for you?
Please share with sc2758g@gmail.com.
On its was @Steve
Page Header 2 column of Work_Orders_Master is the only Show column with Required? set to ON.
No, just an observation. Though I do wonder what the effect of Required? ON for a Show column is.
It is definitely an oversight by me having it on. Should I remove the flag and test again?
Sure.
How do I reproduce the problem?
Nothing jumps out at me so far.
I just removed the Required flag and retested. Same behaviour.
The โAutomate Open Ticketโ action is associated with the โEdit Scheduled Maintenanceโ view (Maintenance > Scheduled Maintenance > Edit Scheduled Maintenance)
I have removed the filter condition from the Slice so you should be able to see all rows in the table if you need to.
damn.
What Iโm wondering is this: thereโs automatic dependent drop-down behavior going on and Iโm thinking that might be fighting the way that the system creates/saves the record in the background.
For example; in your app youโve got a table containing the different options and youโre using a simple valid if formula like this (Table[Option1] for the first column, then Table[Option2] for the 2nd, Table[Option3] etcโฆ) to pull in the appropriate options for the dropdowns.
Appsheet picks up on the dependent nature of things and automatically only shows you the appropriate options for the dropdown depending on what has come before.
So when I select โFurnitureโ only the furniture options appear, nothing for car, kitchen, aircraft, etc.
This is some of the appsheet magic, if you wanted to manually construct this behaviour you would need a valid if formula like this:
select(Table[Option2], [Option1] = [_thisrow].[First_Option_Selection])
Then you would cascade these for your other columns:
select(Table[Option3], [Option2] = [_thisrow].[Second_Option_Selection])
etc.
So Iโm wondering if perhaps this auto-appsheet-magic might be getting in the way???
Unlikely, but magic is always suspect. Worth a shot.
Copy the Automate Open Ticket action and reconfigure the copy as type App: go to another view within this app and set the target expression to:
linktoform(
"work_orders_master_form",
"status", "Open",
"type", [type],
"priority", [priority],
"area", [area],
"category", [category],
"sub category", [sub category],
"asset", [asset],
"work order description", "Scheduled check from Health & Safety Application",
"assigned to", [assigned to],
"expected completion date", (today()+3),
"completed by", [assigned to]
)
which replicates what your action is doing. Then invoke this new action and save the resulting form. Confirm everything looks correct. Asset Number doesnโt look right to me. Also, when I try to sync the changes:
Value 'Preventive' in field 'Type' cannot be converted to type 'Enum'.
Iโm testing on my own copy of your app.
This works fine @Steve thanks. It is a handy workaround but hopefully I can revert to the initial action at some stage.
Do you think the sync issue with Preventive was just the inconsistency in the term between the tables and the ENUM options?
The asset number is one I am trying to work out. I have created an asset register with numbering sequence like 1.1.1.1 and was hopefully going to invoke the next number in the sequence based on the asset categories when the user set up a new asset.
Is it possible to use a MAX expression to present the user with the next number in the chain when creating a new asset or do the multiple decimal points prevent this being possible?
Go ahead and try the automated action again. The LINKTOFORM() action was a troubleshooting tool.
The problem was the spreadsheet was providing values the app is configured to consider invalid. So yes, due to the inconsistency.
Did a resync and repeated same behaviour. Still seem to be getting the same error message, unless I need to do something different.
@Steve. Do you think this needs further investigation as a possible bug or should I try changing my setup? Still seem to be getting the same message when trying to execute the action.
I think itโs worth more investigation. Iโm going to continue looking at it today.
Thank you
I got it to work by removing the Valid If expressions for the Category and Sub Category columns. Progress! More to comeโฆ
I have reproduced the problem to a trivial case. It appears to be, as @MultiTech_Visions hypothesized, due to dependent drop-downs. Iโve escalated the issue internally.
In my trivial case, I created an app with two tables (Table 1 and Table 2) each with two columns (A and B). The columns of the second table have Valid If expressions referencing the corresponding column of the first table (e.g., Table 2 column A used Table 1[A]
). By merit of being adjacent columns referencing adjacent columns, a dependent drop-down relationship is created. I then created an action to add a row in Table 2 from the values of columns A and B of a row in Table 1. Confirming the hypothesis, the action failed. Somewhat surprisingly, if the columns set are listed as B then A, the action succeeds.
More to comeโฆ
Thank you for the update @Steve
Hi @Steve. Were you able to glean anything else from this issue? Just to test (in the hope of being able to proceed ) I built the Valid If formulas in the form manually as @MultiTech_Visions referred to previously but am still being presented with the same error when trying to run the action. This may have been expected, I donโt know.
No further progress over the Labor Day holiday here in the US yesterday. I do think this is a bug and will have to be addressed by the developers. Like I said previously, I have escalated it internally. In the meantime, the best we can hope for you is a work-around. In my testing, the ordering of the columns in the action can affect the behavior: specifically, ordering the dependent columns before the columns they depend on. Try tinkering with the order of the columns in the action.
Hi @Steve. Not sure if it will help with the analysis of this issue but I created a Scheduled Report and added in the โAutomate Open Ticketโ Action, ran a Test and the system recorded an error in the logs. If you think it may help to review the error you are still a Co-Author on the app. The Report I ran a test for is โSM for Maintenance - Every 1 Monthโ.
Iโm not sure if Iโm experiencing precisely the same issue, however, when Iโve experienced the error โCould not set value because column X would become blank and requiredโ, Iโve worked around it by adding a context formula to the REQUIRE? flag. Usually I add something simple like CONTEXT(โViewโ)=TableName_Form
That way when I am actually entering data in a form view the field is required, but when the action executes it doesnโt recognize the field as being required. Note that I have a different form view for the one that executes my action vs the one that actually edits a row.
User | Count |
---|---|
44 | |
28 | |
28 | |
21 | |
13 |