Filtering a drop down list by a another field in the form

[Bryonie Hawkins]

Hi. I'm new to Sailforms. I'm making a database to keep track of students and their progress.

I have 2 tables...A lesson table which has a grade number in it and a scales table which lists all the available scales. I have a form which has a filled in field for grade and a field with a drop down list for the scales. I can get the scales to list, but i want to list only the ones for each grade. I can filter them, but can't figure our how to get the filter to work based on the grade entry.

I hope this makes sense - I'm a long time and repeated beginner with databases (previously did some stuff in Access but was never a power user!)
Thanks for any help you may have for what is probably a very basic question.
Bryonie

[Tim Wallace]

Probably the quickest way to see what you have done and what could/should be done, is for you to backup your project group and share it with the Sailforms users forum, by attaching it to this message.
Note: to attach files to these messages you first need to tap on Desktop below for broswer mode.
Also, it would be helpful if you edit your forms so that they fit on smaller screen sizes. Try Edit Form > Screen Sizes > Medium Phone S3 Layout.
Glad to help - Tim

[MikeG]

Hello Bryonie and welcome,
I assume your Scales field has a Lookup Value or possibly Search-List-Get connection to your Scales table/form.

At the moment I fear you cannot achieve what you would like. Although the Lookup Value or SLG connection allows you to specify a List Query you cannot set up a query that dynamically specifies the required grade. The query can make use of Group Variable to specify the grade, but there is no way of assigning its value at the time it is needed - that is when the user clicks on the scales field.

In the next release (currently in beta testing) this problem will be solved. A new "On Focus" feature will allow you to assign the variable value. I'm not sure when Chris plans to release this new version to the world, but it must be getting close.
Mike

[Doug Packer]

You may consider building an extensive equation. Use nested if commands to compress the solution into one field. If you share details of your intentions, I think you/we can make this automatic.

Alternately, Your equation could yield a key that helps you get your desired value from your table.

Doug

[Bryonie Hawkins]

Thanks so much for your quick reply Tim and for your helpful beginners guide.

I've attached the relevent database. The form you need is called 'Lessons' and the remote table for the lookup is called 'Scales'. I want to put a grade number in the grade box and have a list of applicable scales drop down. If I could have the grade filed in previously and the scale drop down field simply react to that it would be even better.

I tried to delete the original screen add size but it wouldn't delete when I pressed the red x and it keeps defaulting to it... sorry.

Bryonie

[Bryonie Hawkins]

Thanks Doug.
I've attached the database in my answer to Tim. What you suggested sounds a tiny bit complicated but if you gave me specific examples for my database I can probably manage it.
I really appreciate the kind help
Bryonie

[Bryonie Hawkins]

Thanks Mike. I look forward to being able to simplify this in the future. For the moment, if we don't come up with a workaround, I'll just scroll through the list of scales until I find the one I want.
Thanks for the prompt help. Great to have such a helpful community - I'll try not to ask questions that are too daft!
Bryonie

[Tim Wallace]

Hi Bryonie!
I loaded the your attached group, but it seems to be incomplete, there were no forms for Families, Students nor Concerts. Please try again.
Don't worry too much about the Form Layouts, you have to have at least one for each form, but the different screen layouts are, among other things, a convenience for opening a form on different sized phones and tablets. By the way, what screen size are you using?
From what I have seen and heard so far, it seems like you are trying to develop a fairly complex project, which always has the risk of time wasting bloat, mission creep and half finished, abandoned, forgotten developments.
I suggest that you consciously define your primary goal, develop a barebone minimal project, make sure it works as required, and then later, add on all the bells and whistles that you think might be cool to have also.
Allow me to anticipate you interests - it seems that you are responsible for a number of musicians, who attend a number of different events (lessons, rehearsals, concerts), where you need to record their attendance and performance (grades). You then periodically create an individual report detailing events and grades. You want to do this on an Android cellphone, because its quicker and easier than paper and pen or sitting at a PC(true).
You want to use Sailforms Pro - because its the best android database manager(also true!)
Once you define your goal, we can determine the forms your SF project will need.
In the case above, I would suggest
Students with fields for key, name, instrument (in case one of your students plays more than one instrument)
Events with key, type of event, time and date
Grades with key, description ( for example 4 Perfect harmony and rhythm, 3 occasional errors, 2 needs more practice, 1 has obviously not practiced, 0 absent)
Reporting with fields Student key, Event key, Grade key
Let me know if this is what you need as a first step ....
Good Luck, Tim

[MikeG]

Tim,
I think Bryonie has given us just the key part of the total group that relates to the original question - how do we use the Grade field in Lessons to allow selective listing of the Scales values from Scales (i.e. only those applicable to the specified Grade value).

We can ignore form Front Page, and in form Lessons we need concern ourselves only with grade and scales related fields - everything else is not relevant to the problem we have been asked to solve.

Bryonie,
I think there are two approaches, both being attempted (erroneously or incompletely) in form Lessons.

In both cases the Grade field must be an Entry Field, not as shown in Lessons a Remote Field) in which the user specifies the grade. It cannot be a Remote Field as the user must specify the value - it is not simply read from a Remote Form based on a form entry connection specified elsewhere. Grade can be connected by Lookup Value to the corresponding field in the Scales form to give the user a list of allowable Grade values.

Also in both approaches the Scale field must be an Entry Field as there is potentially more than one Scale value for a particular Grade, so the required Scale value cannot be simply read as a Remote Field in the Scales form.

Approach 1, not fully available until the current beta release goes live, is to use Lookup Value for Scales too, but at the moment you will see a list of all Scales values, not just those applicable to the Grade value.

Approach 2 is to use a Remote Table view of Scales with entries matching the Grade value and specify a Custom Menu such that addition of entries is not allowed and clicking on an entry does not dusplay the Scales entry but instead puts the Scales value from the selected Scales entry to the Scales field in the Lessons form.

I'd try to post you an example, but I've lost access to the released version of Sailforms at the moment and something created on the beta would not be compatible with your system.

Mike

[Doug Packer]

Hi Bryonie,

Please forget my earlier post. I had different definitions for Scale and Grade than I think you are using.

What are each?

A scale a musical scale, right?
Is a grade a level of musical experience? If so, is it an attribute of each student?

You are welcome to ignore me if the other guys have it covered. I'm always amazed at how well Mike and Tim get it when I have to ask 30 questions.

Doug

[David Smith]

Hi Bryonie
If you can wait till the next version is released, then I think you will be able to achieve what you need. Did quick test and managed to filter a second drop down list by a another field/list. Basically used a querylist filtered by a variable. This query is used by connection option "Lookup value picker". The variable is set by the new beta feature lost focus/changed command from first drop down. Only problem is chevron as discussed else where - hopefully Chris might be able disable this. In mean time could set variable manually with button but requires user attention.
Regards
David

[Bryonie Hawkins]

Sorry I've been MIA - I've been travelling for the last 24hrs. I will look into the suggestions and see what I can figure out but it may be simpler to just wait for the beta. Thanks so much guys - good to know there's a supportive community if I need it. You never know - maybe one day I'llbe able to help someone else.
Bryonie

[Adam Buggeln]

I worked around this issue with a global variable and filter.  When you select the grade, also set the global variable to that grade.  Create a button that filters the scale table with the grade.   To use, select the grade, then hit the button, then select the scale from the (now) filtered list.