Questionaire linked tables

[Stephen_Monagha]

Dear AppSheet team,

I have a questionaire app connected to AppSheets. This questionaire has different sections so I have split them into different tables. The entries in the questionaire main table have a reference to the sections. I only need a 1 to 1 relationship between the questionaires and sections, as once a section is completed it is only related to one questionaire entry.

When I manually create the links in google sheets and use the app it all works. I can see the sections listed and I can navigate to them. However when I create a new questionaire entry and open it I have to select the sections from the drop down list, or create a new section using the ‘New’ option in the dropdown list. These sections do not have particular names that can be identified easily in a dropdown list and I don’t want to use a dropdown anyway as I think this would be confusing to a user and would allow them to select other peoples answers.

The way I would like it to work is to have a single link e.g. Section 1, and when this is clicked, if an entry exists this entry is shown (which is the way it works now), however if an entry doesn’t exist the user is presented with the form for that section. Basically the 'New" option in the drop down list should be executed if an entry doesn’t exist but without having to select it from the dropdown box.

A formula like this is what I was thinking, but it is now allowed for a column of type ref: “IF([section_id]=’’, LINKTOVIEW(“Section1 Form”), [section_id])”

I have also tried adding an action button to present the form, which works in adding an entry to the sections table, but doesn’t update the questionaire table with the reference ID of the new section entry.

Any help is solving this would be much appreciated.

Thanks,
Stephen

[Steve]

Please explain what you mean by the term “link”:

When I manually create the links in google sheets […]

The way I would like it to work is to have a single link […]

[Stephen_Monagha]

Hi Steve,

thanks for your reply.

by link in google sheets I mean links between rows in Questionaire and section.
i.e. questionaire will have a section_id column, that matches the id value of the section tables entry.

by single link I mean appsheets action. So if I click on the action it will either open a form to create a new section or display the section if one already exists. As I mentioned above the action part works, it’s just there is no reference in the Questionaire table to the section table (understandably, as I haven’t done anything to populate that reference).

The other way of doing this would be to create all the required section rows in google sheet once a new questionaire row was added, and update the questionaire row section_id’s with the unique id’s of the new sections. So if we had 3 sections, then the questionaire row would have a section1_id, section2_id and section3_id all populated with the id of each entry from the 3 section tables. The actual entries in each section table would either be empty or filled with default values.

There might be other ways of doing this as well, or I might be going about this the wrong way. I’m new to appsheets so any help is much appreciated.

Thanks
Stephen

[Steve · Accepted solution]

Okay, based on my interpretation of you app’s structure, I would propose some changes. But first, some assumptions:

1. One row in the questionnaire table is created for each individual who attempts it. No single individual will have more than one row in the questionnaire table.

2. One row in each section’s table is created for each individual. No single individual will have more than one row in a section’s table, but may have a row in each section’s table.

3. Neither the questionnaire table nor the section tables use dereferences to refer to each other.

If those assumptions are correct, I suggest entirely doing away with the “links” (references) between the questionnaire table and the section tables. Instead, carry the key values from the questionnaire table over to the section tables so that the same key value for a questionnaire is also used to identify its section entries. Then implement the “links” (navigation) between the questionnaire entry and its associated section entries using actions of type App: go to another view within this app with a target expression similar to this:

IF(
  ISNOTBLANK(
    LOOKUP(
      [_THISROW].[Questionnaire ID],
      "Section 1 Table",
      "Questionnaire ID",
      "_ROWNUMBER"
    )
  ),
  LINKTOROW(
    [Questionnaire ID],
    "Section 1_Form"
  ),
  LINKTOFORM(
    "Section 1_Form",
    "Questionnaire ID", [Questionnaire ID],
    ...
  )
)

1. LOOKUP([_THISROW].[Questionnaire ID], "Section 1 Table", "Questionnaire ID", "_ROWNUMBER") attempts to find the row in the Section 1 Table table that contains the current questionnaire’s key column value ([_THISROW].[Questionnaire ID]) in the section table’s own Questionnaire ID column (which should be the key column there, too). If such a row is found, the value of that row’s _ROWNUMBER column is returned. See also: LOOKUP().

2. ISNOTBLANK(...) asks whether the result of (1) is not blank. If not blank, a row in Section 1 Table was found with a Questionnaire ID column value equal to the current questionnaire’s own; if blank, no matching row was found. If found, step (3) is done; if not found, step (4) is done. See also: ISNOTBLANK().

3. LINKTOROW([Questionnaire ID], "Section 1_Form") takes the user to the Section 1_Form to display the already-existing row (as determined in (2)) that has a key column value equal to the current questionnaire’s ID. See also: LINKTOROW().

4. LINKTOFORM("Section 1_Form", "Questionnaire ID", [Questionnaire ID], ...) takes the user to the Section 1_Form to create a new row in the table. The new row’s Questionnaire ID column value will be automatically set to the current questionnaire’s ID. The ... in the expression represent optional additional preset column values you may want. See also: LINKTOFORM().

In (3) and (4), note that I’ve assumed you’d use the same key column name, Questionnaire ID, in each table to reflect the identical meaning of the column values. The names needn’t be the same.

In (1), we told LOOKUP() to return the value of the matching row’s _ROWNUMBER column. It doesn’t have to be _ROWNUMBER; it can be any column that is guaranteed to have a value if a matching row is found. The key column would be another good choice. The value doesn’t matter, just that it’s not blank if the row exists.

[Stephen_Monagha]

Hi Steve,

thank you for a great detailed answer. Making those changes got me what I wanted.

Stephen