Re: Adding a new row to another table action

MauriceWhelan · 08-31-2019 06:25 AM

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?

Lynn

Hi @MauriceWhelan Do you have other required columns in your Work_Orders_Master table ? You may need to include them/it

MauriceWhelan

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?

MultiTech

See below

MauriceWhelan

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

MultiTech

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?

Lynn

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

MultiTech

I was just going to ask

Is the value being set by this action actually something?

MauriceWhelan

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?

MauriceWhelan

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.

MultiTech

sure, I can take a look; add multitechvisions@gmail.com as a co-author.

MauriceWhelan

Thank you Matt. I have sent on the invite.

MultiTech

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

MauriceWhelan

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.

Steve

Could I take a look if Aleksi hasn’t already?

MauriceWhelan

Sure @Steve Do you need me to share with your email or is the App ID above enough for you?

Steve

Please share with sc2758g@gmail.com.

MauriceWhelan

On its was @Steve

Steve

Page Header 2 column of Work_Orders_Master is the only Show column with Required? set to ON.

MultiTech

You think that could be causing the issue?

Steve

No, just an observation. Though I do wonder what the effect of Required? ON for a Show column is.

MauriceWhelan

It is definitely an oversight by me having it on. Should I remove the flag and test again?

Steve

Sure.

How do I reproduce the problem?

Nothing jumps out at me so far.

MauriceWhelan

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.

MultiTech

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

Steve

Unlikely, but magic is always suspect. Worth a shot.

Steve

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.

MauriceWhelan

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?

Steve

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.

MauriceWhelan

Did a resync and repeated same behaviour. Still seem to be getting the same error message, unless I need to do something different.

MauriceWhelan

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

Steve

I think it’s worth more investigation. I’m going to continue looking at it today.

MauriceWhelan

Thank you

Steve

I got it to work by removing the Valid If expressions for the Category and Sub Category columns. Progress! More to come…

Steve

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…

MauriceWhelan

Thank you for the update @Steve

MauriceWhelan

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.

Steve

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.

MauriceWhelan

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

APiCC_Conor

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.