linking/relationship - 2 situations also concatenating
Posted: Wed Oct 15, 2014 8:09 amI am trying to convert my grils club SmartList database system to HanDBase with some difficulty.
* Catalog is populated with around 900 records of catalog items. Some of the fields are: Item (text), IDno (text), Inventory (int), Price (float).
* Member contains a record for each of the members. Some of the fields are: Sort (calc) Name (text), Year (text), club (popup), ClubYr (int), Grd (text), other misc fields
The SmartList formula for the sort field is: year+' '+club+'.'+clubYr+' (g:'+grade+')' which results in a string something like this: 13-14 1R.1 (g:.3k)
I might also add the name field to it to insure the records are unique to that child.
* Units contains a record for each year for each member to track the units earned that year. Some of the fields are: sort (calc), Name (text), year (text), U1 (pulled from catalog), U1status (popup), U1dt (date), ..., U9, U9status, u9dt
* Order is populated with all the records for each order for supplies. Some of the fields are: OrdrDt (date), Invoice (text), Item (text), IDno (text), Price (float), Category (Popup), Club (Popup)
* Other databases are: Sponsor (contains a record for each club teacher), Attend (contains a record for each teacher and member present at each meeting / it is populated each week, but can be accessed by Sponsor or Member to view all records for that particular sponsor or member)
Here is what SmartList does:
* Each week attendance is logged in Attend. These records are sorted by date and club. The weekly records can be viewed in Attend or the records for a particular sponsor or member can be viewed by accessing Attend through either Sponsor or Member using the Name field even though the records were created directly in Attend.
* When a member completes a unit, Units is accessed through Member to add/update the Units record for the current year. The Units record accesses Catalog which displays records based on club and a specified view as a type of DBPopup. SmartList allowed the desired View to show from the parent database, so I set up a view that showed only the relevant fields (i.e. Item) and filtered to show only the relevant category (i.e. Category contains activity pages). From the activity page records for that particular club, the desired unit was selected and the unit name is displayed in the Units record. Up to 9 units can be earned in a year so there are 9 fields (U1 through U9) that access Orders to pull the desired unit name into the desired unit field.
BTW, this feature to select a particular view when accessing another database would be extremely useful.
* When an order needs to be placed, in SmartList the number needed would be entered into a Need field for each desired item. However, I would like to do it the following way: Add a record in Orders which accesses Catalog. Catalog displays the specified view (with related fields) and filters the records based on the Club and Category fields (SmartList allowed up to 3 fields to be used to select the desired records, but those fields could be concatenated into one field). A particular Item is selected and several fields from that record are displayed in Orders (Item, IDno, Price, Inventory).
Here is what I need to do the above:
* To do some of this involves concatenating text fields or a combo of text and numeric fields along with strings (see the sort field above). When do you think the concatenate strings feature will be available for Android?
SmartList had pretty basic Condition (IF..Then..Else) and Calc fields as you do, which is nice for beginners. But it also had an Advanced Calc field type that allowed nested If..Then..Else statements with the ability to calculate numeric fields or concatenate text within the Then..Else parts.Any of the fields could be current or prev record. It also had a case statement which allowed consultations within each part (i.e. Sel(Cnt+1,'Total',Fname+' '+Lname,Lname+', '+Fname) where if Sel = 1 the first option is put in the field, etc.). This field type also included additional functions that HanDBase doesn't: round, abs, sign, date, time, year, month, day, weekday, hour, minute, second. It also allowed a color to be assigned the the result, so a negative number could be displayed in red or based on a condition one result could show in blue and another in green, .... Though the color part isn't as critical as the other features, it can draw attention to certain things the user needs to notice.
* I'm not sure how to set it up to display the records in Catalog and retrieve the name of the selected unit for the Units record or the desired fields for the Order record. The First/Last record feature won't work in this situation. If this is possible to do, can you give me an example of how to set it up?
Other really useful features Smartlist had:
* The default sort was tied to the view as it is in HanDBase, but the filters were named so they could be interchanged. A view could be set to default to a specific filter which is like HanDBase, however, it could be temporarily changed by selecting a different filter. (I.E. I'm editing some records and as I finish with one I tap a check box, so the record will be filtered out of the list. Afterwards, I realize I forgot something so to see the record I have to either change views (which means setting and maintaining two identical views with opposite filter settings) to see the hidden record or I have to go into the filters and change the settings, then go back in and change the filters back so I can see the unedited records. A simpler and faster way is to be able to temporally change which filter that view uses to one already set up for another view.
* For the numeric filter condition 'equal to' and 'not equal to' In addition to 'between __ and __' would be very useful and less confusing.
* In addition to the filters setting, SmartList also had a category filter. Example: In Orders, I set fields up to calculate the annual cost for for a girl in each club and I set up a view that filters to show only records with info related to that calculation. To get the cost for a particular club I go to the Annual cost view, then in the Categories drop down I select the particular club I want to see (each record was assigned to a category when it was created (either one I set up or unfiled if I forgot to assign it). With HanDBase I have to change the filter setting each time I want to view a different club's cost (this is a hassle) or I have to set up a different view for each club which involves selecting only the fields I want to display on the list view and edit screen as well as the width on the fields on the list view. I also have to set the filters and sort for each one. And if in the future I need to make changes to any of those things I have to change it in each view. This method involves major work. It would be much much simpler if HanDBase was set up to turn on an extra filter type when a popup field is assigned to it (perhaps when turned on the list view window could be shrortened a bit to add a popup window button to select from the choices of 'All', 'unfiled' or any of the choices in the field's popup list. That way the view and basic filters only have to be set up once and the special filter can be turned off by selecting 'All' or filtered based on one of the other choices. This can be usefl in a lot of situations, not just my case.
Other helpful ideas:
* On the screens to edit fields, it would be nice if 3 icons were displayed to show if is is turned on for list view, edit screen, and export. If any of them don't show or are greyed out that feature is turned off for that field. This would help to see at a glance what fields need to have one of these settings changed to get the desired results.
* On the list view it can get really confusing when the data &/or title for one field runs into the next one. An option to turn on lines between the fields would help with the viewability.
* I haven't purchased the Forms add-on yet, so I don't know if this would apply there or not. On the edit screen, it would be very nice to display the popup list icon with a different color or symbol if the popup list is empty. This would speed up entry if the popup field doesn't have to be accessed just to see if there are any choices there.
I hope these ideas can help make HanDBase more flexible, better and in some cases more user friendly.
* Catalog is populated with around 900 records of catalog items. Some of the fields are: Item (text), IDno (text), Inventory (int), Price (float).
* Member contains a record for each of the members. Some of the fields are: Sort (calc) Name (text), Year (text), club (popup), ClubYr (int), Grd (text), other misc fields
The SmartList formula for the sort field is: year+' '+club+'.'+clubYr+' (g:'+grade+')' which results in a string something like this: 13-14 1R.1 (g:.3k)
I might also add the name field to it to insure the records are unique to that child.
* Units contains a record for each year for each member to track the units earned that year. Some of the fields are: sort (calc), Name (text), year (text), U1 (pulled from catalog), U1status (popup), U1dt (date), ..., U9, U9status, u9dt
* Order is populated with all the records for each order for supplies. Some of the fields are: OrdrDt (date), Invoice (text), Item (text), IDno (text), Price (float), Category (Popup), Club (Popup)
* Other databases are: Sponsor (contains a record for each club teacher), Attend (contains a record for each teacher and member present at each meeting / it is populated each week, but can be accessed by Sponsor or Member to view all records for that particular sponsor or member)
Here is what SmartList does:
* Each week attendance is logged in Attend. These records are sorted by date and club. The weekly records can be viewed in Attend or the records for a particular sponsor or member can be viewed by accessing Attend through either Sponsor or Member using the Name field even though the records were created directly in Attend.
* When a member completes a unit, Units is accessed through Member to add/update the Units record for the current year. The Units record accesses Catalog which displays records based on club and a specified view as a type of DBPopup. SmartList allowed the desired View to show from the parent database, so I set up a view that showed only the relevant fields (i.e. Item) and filtered to show only the relevant category (i.e. Category contains activity pages). From the activity page records for that particular club, the desired unit was selected and the unit name is displayed in the Units record. Up to 9 units can be earned in a year so there are 9 fields (U1 through U9) that access Orders to pull the desired unit name into the desired unit field.
BTW, this feature to select a particular view when accessing another database would be extremely useful.
* When an order needs to be placed, in SmartList the number needed would be entered into a Need field for each desired item. However, I would like to do it the following way: Add a record in Orders which accesses Catalog. Catalog displays the specified view (with related fields) and filters the records based on the Club and Category fields (SmartList allowed up to 3 fields to be used to select the desired records, but those fields could be concatenated into one field). A particular Item is selected and several fields from that record are displayed in Orders (Item, IDno, Price, Inventory).
Here is what I need to do the above:
* To do some of this involves concatenating text fields or a combo of text and numeric fields along with strings (see the sort field above). When do you think the concatenate strings feature will be available for Android?
SmartList had pretty basic Condition (IF..Then..Else) and Calc fields as you do, which is nice for beginners. But it also had an Advanced Calc field type that allowed nested If..Then..Else statements with the ability to calculate numeric fields or concatenate text within the Then..Else parts.Any of the fields could be current or prev record. It also had a case statement which allowed consultations within each part (i.e. Sel(Cnt+1,'Total',Fname+' '+Lname,Lname+', '+Fname) where if Sel = 1 the first option is put in the field, etc.). This field type also included additional functions that HanDBase doesn't: round, abs, sign, date, time, year, month, day, weekday, hour, minute, second. It also allowed a color to be assigned the the result, so a negative number could be displayed in red or based on a condition one result could show in blue and another in green, .... Though the color part isn't as critical as the other features, it can draw attention to certain things the user needs to notice.
* I'm not sure how to set it up to display the records in Catalog and retrieve the name of the selected unit for the Units record or the desired fields for the Order record. The First/Last record feature won't work in this situation. If this is possible to do, can you give me an example of how to set it up?
Other really useful features Smartlist had:
* The default sort was tied to the view as it is in HanDBase, but the filters were named so they could be interchanged. A view could be set to default to a specific filter which is like HanDBase, however, it could be temporarily changed by selecting a different filter. (I.E. I'm editing some records and as I finish with one I tap a check box, so the record will be filtered out of the list. Afterwards, I realize I forgot something so to see the record I have to either change views (which means setting and maintaining two identical views with opposite filter settings) to see the hidden record or I have to go into the filters and change the settings, then go back in and change the filters back so I can see the unedited records. A simpler and faster way is to be able to temporally change which filter that view uses to one already set up for another view.
* For the numeric filter condition 'equal to' and 'not equal to' In addition to 'between __ and __' would be very useful and less confusing.
* In addition to the filters setting, SmartList also had a category filter. Example: In Orders, I set fields up to calculate the annual cost for for a girl in each club and I set up a view that filters to show only records with info related to that calculation. To get the cost for a particular club I go to the Annual cost view, then in the Categories drop down I select the particular club I want to see (each record was assigned to a category when it was created (either one I set up or unfiled if I forgot to assign it). With HanDBase I have to change the filter setting each time I want to view a different club's cost (this is a hassle) or I have to set up a different view for each club which involves selecting only the fields I want to display on the list view and edit screen as well as the width on the fields on the list view. I also have to set the filters and sort for each one. And if in the future I need to make changes to any of those things I have to change it in each view. This method involves major work. It would be much much simpler if HanDBase was set up to turn on an extra filter type when a popup field is assigned to it (perhaps when turned on the list view window could be shrortened a bit to add a popup window button to select from the choices of 'All', 'unfiled' or any of the choices in the field's popup list. That way the view and basic filters only have to be set up once and the special filter can be turned off by selecting 'All' or filtered based on one of the other choices. This can be usefl in a lot of situations, not just my case.
Other helpful ideas:
* On the screens to edit fields, it would be nice if 3 icons were displayed to show if is is turned on for list view, edit screen, and export. If any of them don't show or are greyed out that feature is turned off for that field. This would help to see at a glance what fields need to have one of these settings changed to get the desired results.
* On the list view it can get really confusing when the data &/or title for one field runs into the next one. An option to turn on lines between the fields would help with the viewability.
* I haven't purchased the Forms add-on yet, so I don't know if this would apply there or not. On the edit screen, it would be very nice to display the popup list icon with a different color or symbol if the popup list is empty. This would speed up entry if the popup field doesn't have to be accessed just to see if there are any choices there.
I hope these ideas can help make HanDBase more flexible, better and in some cases more user friendly.