Can variables be dynamic?

Sorry for the slightly strange subject. I’m after some advice.

I have a table containing lots of fields. And have another table which contains further information on those fields. So the first table might have 20 fields in it, the second table will have 20 records with one of the fields containing the field name from the first table. I hope I’ve explained that well enough.

I have a repeat which cycles through those 20 records and I want to set it to show or not show depending on the value of the field in the first table where the record matches it.

Can this be done? If so, what’s the typical method for doing this? Do I need to hand-code or does the Wappler UI accommodate such a technique?

Many thanks.

Jon

Hi Jon. Can you describe the relationship between two tables more precisely, specifying the fields and keys by which the tables are linked?

Thanks @Mr.Rubi.

Here’s the structure…

Table 1 - Questions

Structure
RecordID (int11 auto-increment), questionFieldName (varchar), questionFieldAnswer (varchar)

Records
1, “quQuestion1”, “yes”
2, “quQuestion2”, “no”
3, “quQuestion3”, “yes”
4, “quQuestion4”, “no”
5, “quQuestion5”, “no”

Table 2 - Questionnaire

Structure
QuestionID (int11 auto-increment), quUserID (int11), quQuestion1 (text), quQuestion2 (text), quQuestion3 (text), quQuestion4 (text), quQuestion5 (text)

Records
1, 241, “yes”, “yes”, “no”, “no”, “no”
2, 137, “no”, “yes”, “yes”, “yes”, “yes”
3, 95, “no”, “no”, “yes”, “yes”, “yes”

If user 241 views the page, the repeat goes through the 5 records from the Questions table. I want the display attribute to show when the related question for that user matches the required answer in the loop.

eg. User 241 would see questions 1, 4 and 5. User 137 would see only question 3 and User 95 would see questions 2 and 3.

I’ve really tried to keep this as simple as I can but let me know if it still doesn’t fully make sense.

I don’t really like the structure that you have chosen for the tables, so I will explain the solution through an optimized table structure. I will also try to explain why it is better to optimize the structure.

As for the final result, everything seems to be clear here. You want to match the users ’ responses with the correct answers stored in your system. As I understand it visually it should look like this (in the text field I enter the user ID, which are specified in your example above):

If I understood the task correctly and the result is what you need, then below is the solution of how I achieved this.

First, I will describe what optimization I performed with the table structure:

  1. This is not a mandatory change at all. However, I always do this. Whenever possible, I always try to store only numeric values in fields that can be used in logic. This always simplifies working with these fields and reduces the likelihood of errors. Especially if the fields can be easily converted. So I converted the field “questionFieldAnswer (varchar)” to “questionFieldAnswer (int3)” (replaced “yes” with 1, and “no” with 2). This is how my table “Table 1 - Questions” looks like:
    1
    Data in the table:

  2. More serious transformations in the table “Table 2 - Questionnaire”. The structure that you use is not very effective. With such a structure, you will often encounter problems when you need to build some non-standard logic or perform scaling. This structure will create many problems. Instead of storing each user’s answer to a specific question in a separate field, it is much better and more efficient to store each answer as a separate record. This structure will give excellent flexibility and will not create any problems with non-standard manipulations or in the case when you need to scale (for example, you will want to expand the questionnaire and make not 5 questions, but 50… in your structure you will have to redo everything including the table…). My structure will look like this:
    3
    Briefly by fields. “idA” in your example is “QuestionID (int11 auto-increment)”, “quUserID” is similar to yours, “question_id” this field stores the ID of the question from the “Questions” table, “field_answer” this field stores the user’s answer (takes the values 1 or 2, which is equivalent to" yes" or “no”). Thus, the data that you gave in your example in this table, in my version of the structure, looks like this:

Now, let’s turn to the server action:

  1. The GET variable for filtering users. In your case, the data will be taken from the users table.

  2. At this step, I get all the responses for a specific user. It is important to get only two fields of the question ID and the answer itself. The data is filtered by the user ID. The data is needed only for internal logic, so it is not output. Visually it looks like this:

  3. At this step, i run a repeat for each record from the query above. The output of the fields can be left empty, because the data will be used only inside the repeat:
    7

  4. I make a single query to get a true answer. Just filter the table “Table 1-Questions” using the question ID from the repeat:

  5. At this step, i compare the answers. I get one answer from the query above (this is the true answer) the second answer is how the user answered, it is inside the repeat.

  6. This is also a single query. Just output the user’s response, which was correct. I use the table of questions and filter it using the id of the question that is inside the repeat:

The settings are simple the table. I use repetition as the data source:

In the cell of the “Question” column, i use data from:

In the cell of the “Answer” column, i use the expression:

A dynamic attribute Show must be applied to the entire row of the table to hide empty rows:

5 Likes

Wow. That must be the most comprehensive response I’ve seen here and I can’t thank you enough.

I will go through it all and let you know how I get on.

Regarding the data structure, I actually do have it more like your suggestion already. I changed it in my example to try and simplify my explanation so I apologise if I gave you a whole load more work improving it.

Huge thanks for your time spent on this. I’ll let you know :+1:

1 Like

Thanks again for you time spent on this, @Mr.Rubi . I’ve gone through what you wrote again and I completely agree with your approach.

However, there is a reason why I need a single record to store all the answers and that’s because each answer has it’s own unique structure. Some are binary yes/no, some are text inputs, some are more complex than that and have additional questions based on another answer. And the design of the questionnaire page is very detailed for each question. It just won’t work looping through a query of questions in this instance.

Therefore, my need to use the value of one record as the variable name to check another still stands. I’m after the way to achieve this in Wappler.

Is this possible? How would I actually achieve it?

I think this is related to a question I asked long ago, for which there didn’t seem to be a ‘Wappler solution’. However, this recent thread looked like it might offer a solution (I couldn’t get it to work but it might be worth looking at more closely - if indeed it is relevant to what you need).

To get the best final result, I suggest the following:

  1. First, let’s think together about improving your structure. Because it is the improvement of the structure that gives a qualitative and long-term effect in the work. The work with logic is simplified, the tasks of scaling in the future are simplified, and the work becomes more stable and flexible. The structure I proposed above can work not only with binary questions. The proposed structure can serve any questions and even a unique questionnaire (when each user has unique questions that differ from the questions of other users). If, after all, the proposed structure cannot serve some specific task, tell us more about this task, we will try to optimize the structure for it.

  2. If, after all, the problem can not be solved by optimizing the structure of data tables. Let’s move on to the solution at the logic level. We will try to find a solution based on your table structure.

Can I simplify my question?

I want to change it like this:

Normal code generated by Wappler…

dmx-show="(connQuestionnaire.data.queryQuestionnaire.subconQuest1 == FieldAnswer)"

I want the subconQuest1 to instead be pulled in from a field in the loop, eg:

dmx-show="(connQuestionnaire.data.queryQuestionnaire.{{loopQuestion}} == FieldAnswer)"

but I’m not sure what the syntax should be. I’ve shown it here in curly braces purely to illustrate my intention.

@Teodor or @patrick, how can I achieve this?

Ooh, that looks very interesting. It implies what I want to achieve is possible but I can’t see exactly how I should go about it.

Before I give up on my plan and do it a completely different way, could someone please confirm if this is or isn’t possible?

In principal, it seems possible, given that there seems to be a way for Wappler to parse dynamic variables, using the custom formatter included in one of the threads I mentioned. Did you try this? I couldn’t get it to work. Perhaps there was an error in the formatter, but presumably @patrick suggested it because he knew it was possible. I think it is/would be a great feature.

1 Like

I did look at that thread but wasn’t able to solve the issue with it.

I keep thinking that what I want must be possible and I can reduce the finished code to a fraction of the size by using this technique, and make ongoing maintenance so much easier, but I’ve tried lots of ways but without success.

I agree that Patrick is the one to show how it’s done or confirm that it’s not possible. I’ve now started doing it a completely different way which will take much longer to complete (it’s a huge questionnaire which is why I want to do it using data) but at least I know it will work.

Would love to do it this way, though.

Tom, if I understand your need correctly, then the solution to your problem is quite simple. You should save your string not in a variable, but in an array with conversion to this very array.

It looks like this:

<dmx-array id="arr1"
    dmx-bind:items="(serverconnect1.data.query.fieldA+','+serverconnect1.data.query.fieldB+','+serverconnect1.data.query.fieldC).split(',')">
</dmx-array>

Then just create a repeat that uses dmx-repeat:repeat1="arr1.items" as the data source, and as the text dmx-text="$value".

Jon, if I understand your task correctly (I adhere to the understanding that you need to compare and show the correct answers from each user, if this is not the case, correct me), then you have a more complex task.

I don’t quite understand when you talk about a loop, what do you mean here?:

Sorry @Mr.Rubi, when I refer to ‘loop’ I probably should say ‘repeat’.

Repeat always works with an array. Combining the fields quQuestion1, quQuestion2, quQuestion3, quQuestion4, quQuestion5 into an array and then using it in a repeat is not a problem. The problem is to correctly match the rows from this array with the correct questions. After combining the fields into an array, we will not know which row belongs to which question.

Thanks for this suggestion. It’s an interesting idea but unfortunately I don’t think it’s a solution. The example I gave in that thread from 2019 was a simplification.

In this case, I wanted to retrieve a value stored in single field in a database and have Wappler parse this rather than treat it as static text. The single value is in effect a template - perhaps something like this:
image
(I used an image as I couldn’t get the code to display correctly)

The code could actually originate from anywhere - not necessarily as described above. This can of course be done using PHP as the code is included in the page before Wappler parses it. I assumed it wasn’t possible in Wappler, until I saw the other thread I referenced above, where @patrick seemed to offer a solution for something similar, but I couldn’t get this to work.

I think there would be many uses for such a feature (eg as discussed in this current thread). Really, it’s just a general request - an option to get Wappler to treat a string as text to be parsed, in the same way as dmx-html tells Wappler to treat dynamic data as HTML. (Perhaps I should create a request for this - unless it’s already possible.)

Create a request :+1:. It’s something that would be really handy - unless it’s already possible and we just don’t know how to do it!

Yes, Tom, I initially misunderstood your task. My solution is not suitable for this.

Perhaps this is deja vu, but as if 3-4 weeks ago I read a topic in which this question was raised. I couldn’t find this topic. Such a function would really be useful in cases where it is necessary to implement CMS functionality in a project. I myself will need to implement this functionality in the project, because the client wants a built-in editor in his CRM, in which he can configure not only the contents of certain sections, but also the design of elements in these sections. Therefore, I will also need the ability to read the code written to the database and recognize areas with dynamic data in the code.

2 Likes

It’s good to hear your requirement is similar to mine - and probably to @sitestreet’s too. For anyone developing a system where such flexibility is required, a feature like this would be invaluable -particulary if such a system might be used by multiple businesses, where different users within those businesses might need their own options to customise templates etc.

I’ll create a request, but out of interest are you using PHP? I am. I wonder if it’s something that is already possible with NodeJS (about which I know very little).