linking/relationship - 2 situations also concatenating

This forum is for application developers who are integrating HanDBase into their solutions via the HanDBase API (HAPI) or other means. It's also a great place to discuss runtime development.

linking/relationship - 2 situations also concatenating

Postby shalom » Wed Oct 15, 2014 8:09 am

I 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.
Posts: 34
Joined: Mon Sep 29, 2014 9:42 pm

Re: linking/relationship - 2 situations also concatenating

Postby shalom » Thu Oct 16, 2014 10:09 pm

I figured out how to pull the unit names into the units record to track completed units. I think I also figured out how to pull select a record and puss several fields from that record. However, the ability to tell it what view to use and the ability to limit the fields that appear based on 1 to 3 fields (3 fields is betterm but 1 field will work if concatenation is added for Android) in each database would be extremely useful and would make it easier for the end user. Both of these features would also make it possible to use one database as a popup database for several fields by setting up pairs of fields (one field to hold the filter criteria and another to hold the popup data).

I put a question under the Android section that I really need an answer to. I'm not sure how to implement that situation. Thanks.
Posts: 34
Joined: Mon Sep 29, 2014 9:42 pm

Re: linking/relationship - 2 situations also concatenating

Postby dhaupert » Sat Oct 18, 2014 11:12 am

Hi there,

Sorry for the delayed reply- your post was a bit of a challenge to respond to!

First off, thanks for the detailed set of thoughts and info. So happy you're giving HanDBase a try. Most of your suggestions for the program are highly complex and disruptive to the database format so the likelihood of seeing this in any update any time soon is pretty low. The one definite exception is the Concatenation field as it's part of the coming external field type. I have been working on this and hope to release something in the next few months. With that field type you may be able to accomplish at least some of what you're after, and if I can pull off what I'm hoping to, you may see a bunch more useful field types coming in the months to follow.

Hope this helps
Posts: 4113
Joined: Tue May 26, 2009 11:51 am

Re: linking/relationship - 2 situations also concatenating

Postby shalom » Sat Oct 18, 2014 3:45 pm

A BIG YEAH for the concatenation!!!! Do you have an ETA for the update?

Did you get the file for testing the change from calculated to conditional type?

I realize some of what I would like to see implemented can't be done "overnight", but they could add more power and flexibility to the application. Here are some possible ideas for implementation:
For the view and filter options in the DBPopup and Link/Linked types, for backward compatibility if the new features are blank the system could default to the current method. Or new field types with these features could be added. The DBPopup just needs the view and filter options. You are already doing something similar to the filter in the Relationship type for the filtering (comparing a field in each database and returning a different field). The views already have names assigned to them which could be used in the setup for the view to be used. The Link/Linked type may just need view option and the extra field to use as a filter if the primary link is made on one field such as the name field and the records can be filtered/limited by another field. These things may take some time to implement, but they don't seem too difficult to do. Of course I don't know Android programing (yet), nor do I know the specifics of how you have designed the porgramming (what's under the hood so to speak). So I could be wrong about the difficulty. I just had a thought. If you could use some help programming for the Android system I'm willing. With the right documentation and tools it shouldn't take long for me to learn Android programming.

BTW, it would also be useful to have a screen that can be accessed from the menu to view info the user may need to know about the database and icons to popup help info for individual fields.
Posts: 34
Joined: Mon Sep 29, 2014 9:42 pm

Return to HanDBase - Developers

Who is online

Users browsing this forum: No registered users and 4 guests