As an update to my thinking, I think it may make more sense to do conditional value lists…
Right now my plan is for users to select the list of services that an organization provides using a checkbox control type. The IDS for each of the services selected are then stored in the org_services table. I think I then need to institute four sets of conditional value lists based on the services selected using the checkbox. Is it possible to create a conditional value list based on multiple answers from selections in a checkbox? (note: my four sets of conditional value lists would not be hierarchical- meaning that the second is dependent upon responses from the first etc. Instead all four are derived from service selections made in the checkbox control type.)
I don't really have a clear picture of the tables and relationships involved so this may not be the best approach, but....
Conditional valuelists are based on a relationship where a value selected in a field in table1 (usually the layout or portal's table) controls what values from a related table are visible in the list. Here's a fact you may not know. If you set up the field from table1 as a checkbox field and select more than one value, the values are stored in the field separated by returns. When a field with such a list of return separated values is used in a relationship, it matches to fields in the related table if any one of the listed values matches the value of the key field in the related table.
Example: If I select Apple and Orange check boxes in such a field, my value list will list both the relatd values for Apple and also the related values for Orange.
Thank you for this information. This was the assurance I needed that while my answers from the multi select are stored in one field separated by returns they will still be searchable when I start creating reports. In my literal view of relationships I would rather they have separate entries in the table but in the end it makes no difference b/c the end result will be the same.
Storing the values in separate records are much more flexible for some reporting needs.
Keep in mind that I said "this may not be the best approach". It depends on how your data is structured and the things you need to do with it.
Sure. I understand that your suggestions need to come with the caveat that you can't be omniscent about a db that you've never seen the structure for. :-) But I appreciate the word of caution because I want to plan for the maximum flexibility possible from a reporting perspective if this project takes off the way I would like. My main reason for doing this is to try to figure out what story my data is telling me.
Right now I am considering a checkbox option for users to select all the possible ages that could use a particular service. It would be easier for users to simply enter an age range for each service an org offers such as ages 2-5 for example. The one issue I have with this is that in some cases the age ranges start in months because these are services for infants through school age children. Would there be a way for me to attempt an age range field that users could enter?
And again thanks much for any time spent on my behalf.
You can do this one of two ways that I can think of right off the top of my head:
Use a text field for entering/storing the range, but then calculation fields extract the start and end values in separate fields for use in constructing value ranged finds or in relationships based on a range of values.
Have the user enter a range directly in the two separate fields to begin with and then use a calculation field to combine the two values for display/reporting purposes.
To handle ranges that refer to months or years, you can add an additional field for both the start and end values where a value list is used to select the units (years or months). Then a hidden calcluation field converts the entered value into a standardized value that works for both years and months:
If ( StartUnits = "months" ; StartDataEntry / 12 ; StartDataEntry ) // converts start values entered as months into a fraction of a year.
is one possible calculation for such a field.
Thank you. This calculation worked like a charm. For posterity's sake here is what I did:
Purpose: I needed to create a way for users to enter the age ranges that specific services cover. The age range could be entered in months and/or years. In this case, using calculated date fields would not make sense.
There is probably a more elegant way to implement this but here is what I did:
1)I added 6 new fields to my table:
2)I created a value list of months|years. On the layout I implemented a popup control type for each of the time_unit fields and assigned the mnths|years value list.
3)I assigned each of the calc fields the calculation provided by Philmodjunk. (These are hidden and will be used for reporting purposes.)
(tip: pay attention to whatever your field names are if using this calc for your purposes.)
This enabled me to use a portal for this particular table in my layout, which is a timesaver for data entry.
shew… I am making my way up the learning curve.