Display of results based on drop down list selections

Karen_Barton · August 22, 2021, 6:27am

Hi Folks

I’m wanting to display a list of results based on the information that I choose from a drop down.

The data is stored in the database in a collection called ‘selections’,

My page has the following dropdown fields:
Round, Day, Side. For the 3 fields, I’ve used a formula of Unique_by_key and MAP to return the data from the ‘selections’ collection.

How do I get the “result” to display information with labels as:
Team 1
Lead: team 1 leads name
Second: team 1 seconds name
Third: team 1 thirds name
Skip: team 1 skips name

Team 2
Lead: team 2 leads name
Second: team 2 seconds name
Third: team 2 thirds name
Skip: team 2 skips name

etc,
there would be a maximum of 4 teams, depending on which day is selected.
Some sample data is:
{
“select_day”: “Saturday”,
“select_team”: “3”,
“select_lead”: “Team 3 leads name”,
“select_second”: “Team 3 seconds name”,
“select_third”: “Team 3 thirds name”,
“select_skip”: “Team 3 skips name”,
“select_side”: “1”,
“select_round”: “1”
},

I have no idea on where I should begin with this, so hoping to be pointed in the right direction.

Thank you.
Karen

Karen_Barton · September 12, 2021, 7:22am

Is this at any way possible?
Am I better off using the javacript flow logic instead of a formula?

Dimos_Vamvourellis · September 12, 2021, 10:28am

check out this tutorial maybe its what youre trying to do

Karen_Barton · September 14, 2021, 2:04am

Thank you for getting back to me on this.

I found that powerup video, after I asked the question as to whether my “problem” could be solved or not.
I’ll take a look at it his weekend and will let you know if I have any issues.

Thanks
Karen

Jose_Adolfo_Villalob · September 14, 2021, 10:05pm

The easiest approach to the problem is as follows:

One. Manipulate the data in the database so that you feed AppGyver a list of objects with the following format

Team 1
Lead: team 1 leads name
Second: team 1 seconds name
Third: team 1 thirds name
Skip: team 1 skips name

Team 2
Lead: team 2 leads name
Second: team 2 seconds name
Third: team 2 thirds name
Skip: team 2 skips name

To accomplish this, use SQL SELECT and GROUP BY team name

Two. Display this list using nested containers, as follows::

  Outer Container (use Repeat_With = selections)

     Paragraph: ( use Label = team name)

     Inner Container ( use Repeat_With: current)

          Use a List to to display the contents of a team ( use List of Items in Repeat)

Three. Use the dropdowns as simple filters, tying the Visible field of the list (or the inner container) to a page variable with the dropdown selection.

Hope this helps

Karen_Barton · September 15, 2021, 5:35am

Thank you for that. I shall give it a go this weekend.
That all makes sense except for point 1 (I’m using restdb.io as my database). I’m assuming that will create pageVar with a formula doing the select and group by.
But, let me have a go first and I’ll come back to you.

Karen_Barton · September 20, 2021, 6:29am

Thank you for your help with that. That works perfect.

Karen_Barton · October 12, 2021, 8:06am

Hi Jose_Aolfo_Villalob.

Your solution is perfect. However, I missed two fields which I now need to include.
They are opposition and whether its a home/away game.

I have used a separate container, and used the below formula in the Advanced > visible area.
CONTAINS(LOWERCASE(data.newselections[0].fixture[0].fixture_day), LOWERCASE(pageVars.TeamSelections.TeamDay)) && CONTAINS(data.newselections[0].fixture[0].fixture_date, LOWERCASE(pageVars.TeamSelections.TeamDate)) && CONTAINS(data.newselections[0].fixture[0].fixture_side, LOWERCASE(pageVars.TeamSelections.TeamSide))

for testing purposes, I have two records/documents in the database, I use the filters as per above and I return 1 entry with the Opposition and home/away. But for the other I do not.
In both instances, I do get my team selections, as per above.

For the opposition paragraph I’m using the formula to grab the data variable
data.newselections[0].fixture[0].fixture_opposition

and for the home/away I’m also using a formula
IF(data.newselections[0].fixture[0].fixture_home == “True”, “Home”, “Away”)

I’m not sure why I get the opposition or home/away comes up for one record and not the other.
What am I missing?

Jose_Adolfo_Villalob · October 12, 2021, 8:30pm

Hi, Karen,
I am not sure i understand the problem. Two thoughts come to mind:

If the two new fields are just part of the original list, i don’t see why you need to use a separate container.
Is this new container and inner container to the original data list container? When you nest more that two containers it can get really tricky.
You have a formula for the home/away:
IF(data.newselections[0].fixture[0].fixture_home == “True”, “Home”, “Away”). I assume that fixture_home is a true/false field. The formula should be: IF(data.newselections[0].fixture[0].fixture_home == true, “Home”, “Away”)
If this does not help, you can give me more details and i will be happy to help

Karen_Barton · October 13, 2021, 2:04am

Hi Jose
Thank you for responding.

my data from my original request was all coming out of one collection called, selections. But, ‘Selections’ didn’t include the fixture_opposition or fixture_home (they were in the fixtures collection) I’ve changed my data structure, so that it looks like:

I’m hoping thats a better way to handle it.

The new container displays as per below

I tried just having the field on the page excluding a container, I wasn’t sure on the formula I should use as it depended on what was selected in the filters to produce the result.

Thanks for the formula update, Yes, the fixture_home is a true/false field.
Case sensitivity will get ya all the time.

Hopefully I’ve explained that better.

Jose_Adolfo_Villalob · October 13, 2021, 10:45pm

Hi, Karen,
I understand your approach a little better. Although I haven’t discovered any specific problem, i pose a couple of suggestions.

I notice that your selection formulas use CONTAIN statements. You need to carefully verify the consistency of your data (in the page variables and the database”. CONTAiN can through unexpected results. Let me give you an eaxmple taken from one of your statements:
CONTAINS(data.newselections[0].fixture[0].fixture_side,LOWERCASE(pageVars.TeamSelections.TeamSide))
A) if fixture_side = “02” and TeamSide = “2”, the result is true, but
if fixture_side = “2” and TeamSide = “02”, the result is false
I suspect something like this is causing the problem that one of your test records shows correctly, but the other does not.
B) Another possible inconsistency in the data. you mentioned that fixture_home was boolean, but the data field in your example is text: “True”.

Hope this helps

Karen_Barton · October 14, 2021, 1:09am

Thanks Jose.
Yes, I noticed that my data has “True” instead of “true”. I have now fixed that.
I also fixed “False” as that should be null.
In terms of fixture_side = “02” and TeamSide = “2” that will result as true as the user must select those values from a drop down/combo box.

I’m wondering…
Would it be something to do with the Boolean value false being Null?
When the selection filters result is true, everything displays as in fixture_opposition, fixture_home and teams.
Where as, if fixture_home is false/null then I do not get the fixture_opposition or home/away, but I do get the team selection.

Fixture_home is false/null

Fixture_home is true

Jose_Adolfo_Villalob · October 14, 2021, 3:29am

Hi, Karen. Your suspicion is correct. If a variable is defined as boolean, it can take only one of two values: true or false ( no quotes). You cannot assign null to it and you cannot assign “True” or “False”, “true” or “false” as a text string. A boolean understand only true/false.

Karen_Barton · October 14, 2021, 4:42am

Thanks Jose.
hmm, so, the database field is boolean, but the variable is text.
That means I’m best to change my database field to a text field with the values of “Home” or “Away”?
or, is there another way I could look at?

Jose_Adolfo_Villalob · October 16, 2021, 8:46pm

Hi, Karen:
You can either change the database fields from boolean to string, or you can use formulas to change the boolean values into string values. It all depends on how much flexibility you have to modify your app and database.

Karen_Barton · October 16, 2021, 9:55pm

Thanks Jose.

Yesterday I was playing around with text fields and couldn’t get it work either. Unless for some reason the database is holding onto a Boolean value somewhere. I will dive deeper into that today.

In relation to the formula to convert a Boolean value to a string. I thought that’s what my IF formula was doing. Which formula function should I be looking at for that?

Karen_Barton · November 10, 2021, 7:50am

Hi Jose
Unfortunately, I’m no closer to getting this resolved.
Instead of using contains in the formula, would I be better using is_equals?
If so, what would that formula look like.

The fixture_home field is now text only and contains “home” and “Away”, this information comes from the dropdown box.

Jose_Adolfo_Villalob · November 10, 2021, 11:26pm

Hi, Karen,
Sorry to hear you are still having problems with queries. Using is_equal or == will probably not help because the equal is even less forgiving than CONTAINS. They is a deeper problem with the data, or their format, that is causing the problem. I have the following suggestions:

Take out the Contain statement in those variables that fail, run the statements, and examine the results you obtain.
Insert Alerts in the workflows to display the data from the variables that fail
Go to the Data section, select the API point that connects to the database, and run. TEST. This will tell you exactly the type of data you are feeding into de app. By the way, be sure you save the SCHEMA FROM RESPONSE. I have had a couple of cases when I forgot, and the data never reached the app.
As a last resort, use Debug to see how the data is being read from the database

Adolfo