When doing a check add row to another table with parts of this check

[Joel_Tjon-A-Tsi]

Hi Appsheet community,

I have created a form for the Technical Guys to do there inspection. Each form starts with “Location” and “room”. The location is automatically filled (depending who is logged-in), he or she can then select a room.

The form holds about 50 Questions. The question field look like this:

Column
“Room_Q1” - Looks to the “user name_Location” and then selects the first question of that location.
“Room_A1” - Is an answer “Checked”, “Fixed”, “Problem”
“Room_P1” - If “Room_A1” is “Problem” take picture
“Room_C1” - If “Room_A1” is “Problem” leave comment.

This repeats 50 times.

We have a Table in the sql database where all “problems” are stored. This Table is synced with a ticketsystem in salesforce. What we want to create is a form in Appsheet and sync only the problem issues as a problem in the problem table, which is then synced with salesforce.

(and in case you are wondering, why not load the salesforce table directly into your app,…We build the application on a sql database because of the sync time)

So,…to the solution,…(all most then)…

I want to create a action: Create problem in problem table: where i set the action to:

Now,… i think i need to do this for each Question separately, creat a new action i mean. Am i on the right path? Or missing some thing?

Or,… is there an easier way?

gr Joel,

[Arthur_Rallu]

Hi Joel,

I’m not sure I understand what your data looks like.
Are you saying that you have 50 questions and each question is in a separate table with 4 columns? I doubt that’s the case, but if it were, you probably would have to create 50 different actions.
If all 50 questions are stored in 1 table, you should be able to only deal with 1 action. Please describe what your table looks like and I’ll try to help you.

On a side note, what is the sync time you are observing and what would you like it to be in order to sync directly with your salesforce table?

Thanks

[Joel_Tjon-A-Tsi]

Hi Arthur,

I have a main form “Overview”, in that form there are two related tables “Room check” and “Bathroom check”.

In the “Room Check” it loads the questions of that locations, and the table looks like this:

The 4 columns Room_Q1,Room_A1,Room_P1 and Room_C1, repeats about 50 times. so the next is Room_Q2,Room_A2, ect ect ect.

Q=Question
A=Answer
P=Picture
C=Comment

What i try to achieve is when the Answer is “Problem!”, a action should create a problem on our tickets list:

Am i on the right track? Because i haven’t got this working yet,…

Answer to your side note: Before we swithced over from a pro license to business we connected the pro version to Salesforce, one-drive and smartsheet. One of the main issues was the syncing time within the app. So when we switched over to the business environment is to connect the appsheet to a sql date base that works as a “Middle ware” functions between different applications.

hope you can help me out!

gr Joel

[Joel_Tjon-A-Tsi]

@WillowMobileSystems John! Any ideas on this topic?

gr Joel

[WillowMobileSys]

Sorry for the late response. I have been wicked busy over the past several days.

I do agree with @Steve and @Arthur_Rallu assessment if you are set on using Actions, but…IT IS A LOT OF WORK! and will bloat your application causing slowness and be more difficult to maintain.

Based on your current data structure…here’s an idea that might make things easier. (Others please chime in). It is still some work to setup and definitely not proven.

The idea is to use an action to kick off with the first column set and then advance to the next set of question columns automatically.

Create a single Form with ALL of the question columns included. Add an indicator column - just a number from 1 to 50+ for each set of columns. Make each set of columns visible ONLY IF the the indicator corresponds to that set of columns.

The first time you launch the form, Initial Value will set the indicator to 1(only time can use Initial value in this approach). When you save, use a Group Action (attached to the Form Saved property) that will:

• Bump the indicator by 1.
• Navigate back to the same Form.

With the indicator set to 2, now only the second set of questions show to the user. Keep in mind that the row will now be in Edit mode as it was already saved so Initial Values won’t work.

Continue with the above, saving and relaunching, until all sets of questions have been answered.

To stop the flow, set the Group Actions’ “only of this condition is true” expression to only execute if indicator is <= # of column sets.

If a user cancels out, the row will be current up to the last save. Tapping an Action to relaunch the form will just pick up where they left off.

---

While the above might help with your current issue of getting the questions in front of the users, the data structure may cause you issues down the road. For example, think of what happens when the question list needs to change?

If you are open to re-structuring the data, I think we can get you to a design that will make the whole app experience much better for you as a developer and the end users.

You can post here or feel free to reach out to me directly if you like.

[Steve]

WillowMobileSystems:

If you are open to re-structuring the data, I think we can get you to a design that will make the whole app experience much better for you as a developer and the end users.

This.

[Joel_Tjon-A-Tsi]

@WillowMobileSystems I will first try to make the action by question level, and will test how much time the sync will take.

A bit of background information: The form is now structured that it could contain all (max 50) questions per location. Each location has a question list. So it doesn’t matter if i have 1, 12, 50, or a 100 locations. The architecture of the form will stay the same. Also each location can manage there own questions. This is one of the main reasons why it’s structured this way.

Second:The answer to a question could be (in this particular case) “CHECKED”, “FIXED” or “PROBLEM”. All tree out-come answers are equally important to know on a management level. If i understand your brilliant suggestion this would still be the case, the only difference is that the form will be saved after answering each question?

FYI: The indicator Column as suggested, is just one column, right?

gr Joel,

[WillowMobileSys]

Joel_Tjon-A-Tsien:

Each location has a question list. So it doesn’t matter if i have 1, 12, 50, or a 100 locations. The architecture of the form will stay the same. Also each location can manage there own questions. This is one of the main reasons why it’s structured this way.

Where are all of these questions by locations housed?

Joel_Tjon-A-Tsien:

Second:The answer to a question could be (in this particular case) “CHECKED”, “FIXED” or “PROBLEM”. All tree out-come answers are equally important to know on a management level. If i understand your brilliant suggestion this would still be the case, the only difference is that the form will be saved after answering each question?

I don’t know how brilliant it is. Just brainstorming for something to work within your data structure to lighten the work AND easy to use.

The function of the 4 columns for each question should work the same. So, if the Answer is “Problem” and you need a Picture and a Comment, you would present these as needed and require them to be completed before the save for that question set of columns.

EDITED: Yes, a save after each set of question columns have been presented - so 50 saves.

Joel_Tjon-A-Tsien:

FYI: The indicator Column as suggested, is just one column, right?

Yes, it was intended to be a single column.

[Steve]

Joel_Tjon-A-Tsien:

i think i need to do this for each Question separately, creat a new action i mean.

Correct!

[Arthur_Rallu]

If your other table stores each question separately, as Steve said, you’ll have to create a separate action for each question.
If your other table stores each question in the same way you have your TD_Room set up, you could stick with a single action, but from the look of your screenshot that’s not your case here.

Avoiding the use of 50 different actions would require you to consider changing how your tables are organized at this point.