Hi, I want to create a choices field in database A with field ‘Choice’ which acts like a dropdown but fetching the available choices from a second database with fields called “Activity”.
I studied the help files, tried several things but nothing works. Can you help me?
let activityChoices=arraydeduplicate(arraybuild(cr(),"second database",{Activity}),cr())
would put your second database’s entries into local variable activityChoices, which could then be plugged into an appropriate popup object or statement on a form. If you want to recalculate everytime just put that formula into the statement or object code instead of the variable.
Now if you want to do this on the datasheet, using the built in choices datatype mechanism you can change the choice list stored in field properties with the setfieldproperties statement. I don’t think you can store the formula in field properties to calculate your choices on the fly. That statement in general could be run from either database, but Help’s fine print says you can’t use it to change TYPE or CHOICES in a different database. But you probably could set up the second database to run a procedure whenever your field Activity or its derived list of choices, changes, recalculate your choices list then switch to the first database and use setfieldproperties to redefine them. There are likely multiple ways of doing that; setting a timer that checks for a change might be the easiest.
I’m not sure if your other database is named Activity or if that’s a field name, so using more or less generic names, I’d suggest starting with:
arraybuild(cr(),"OtherDB",{FieldName})
If the choices in that field are all unique, that should work. If they occur multiple times, then you can expand on that to get rid of dupes:
arraydeduplicate(arraybuild(cr(),"OtherDB",{FieldName}),cr())
That should give you the list of choices for your menu.
Panorama doesn’t have a built in feature to do this, so there’s no “just click” answer. However, there are multiple possibilities for implementing this using the building blocks Panorama provides. Before giving specific recommendations, we would need some additional information:
- Approximately how many available choices will there be? A handful? dozens? hundreds?
- How frequently will the available choices change?
- Will you be using the data sheet, a form, or both?
Also, you mention “choices field”, I’m not sure if you are aware that that is a specific special type of field in Panorama. I’m wondering if that is what you mean, or you just mean a text field that may have different values in it. Your solution will probably be a lot simpler if a text field will work for you.
The suggestions about using arraybuild( are good but only part of your overall solution. However, instead of arraybuild( I would suggest using listchoices(. EDIT: Oh never mind, I forgot that listchoices( doesn’t allow you to specify a different database. Thank you to Jim Cook for pointing this out to me.
To recommend the rest of the solution we’ll need the answers to the questions I outlined above.
Hi Jim,
I am working on creating a straightforward scheduling tool for volunteers at an annual children’s event. We expect around 170 volunteers to participate in 20 activities spread over 10 half-day sessions. The activities will remain mostly consistent, with the possibility of one or two being swapped out occasionally.
I plan to use a form to show/edit each record in the volunteer database. The database already contains ten fields for activities (in text format), where we can input the activity (possibly using a dropdown menu). Additionally, I will generate reports to create lists of volunteers for each event and each half-day segments. Therefore, I believe it is essential to begin with the activity database.
I realize this is more information than you requested, but I hope it provides some helpful context. I’m new to Panorama X, so please bear with me if my questions seem basic.
regards Jan
You have several choices, but it sounds like a button with the popupatmouse statement might be what you want. The code in the button would be something like this:
let activityChoices = arraydeduplicate(arraybuild(cr(),"second database",{Activity}),cr())
let activityChoice = ""
popupatmouse activityChoices,"",activityChoice
if activityChoice="" return endif
Activity = activityChoice
You mentioned that there are ten fields for activity - are these line items? Perhaps something like Activity1, Activity2, Activity3 etc.? If so, you could change the last line to:
let nextActivityNumber = emptylineitemnumber("Activity")
set "Activity"+nextActivityNumber,activityChoice
That way you could have just one button, and one pop-up menu. Each time you made a choice it would add the next activity.
You could use a push button for this, but I would suggest using a Text Display object with a Font Awesome icon. Or you could also use an image.
Thanks Jim!
The first piece of code works fine for me. The activities are line-items but having just one button makes changing one item (rescheduling a volunteer) very complex. I created 10 small Font Awesome Icons. That does the job!
Sure, that also works 