View on distinct values

[rmuetze]

Hello,

a view lists all data depending on the enabled filters. But I can not figure out how to reduce the result set to distinct values, like you know form SQL "SELECT DISTINCT name FROM orders".

Is there a way to do a "select distinct" somehow different?

mfg, rm

[Brian_Houghton]

Hello,

Thanks for writing. Since HanDBase supports up to 6 active filters at one time, you could apply more filters to the view, so that with each filter the number of matching records is refined/reduced.

[rmuetze]

Hello Brian_Houghton,

thanks for the quick reply. My handbase solution operates a 1:n relationship. The question now is to get distinct values for the n-datasets. It would be hard to define a view for each required distinct value and this could get confusing, too.

There is a request in the "What Feature do you most want to see in HanDBase for Android" which might be my solution - see "Grouping in the list view". This could resolve my issue. But with currently 4 votes .... I have to wait.

Thanks anyway.


kind regards, rm

[Avi]

rmeutze, I'm not sure I understand quite what you are trying to accomplish, but perhaps you could try SORTing on the field containing the data you are trying to filter, and then scroll down the list till you find the name you want?

-avi

[DB100]

In SQL you can use DISTINCT to eliminate duplicate rows.

Assume a database called 'Person':
Name
City
State

'SELECT State from Person' would return all States filled in.
It might look like:
New York
New York
Maine
Maine
Maine

'SELECT DISTINCT State from person'
Would return:
New York
Maine

I'm not clear if that is what he's looking for.

How about a dummy field called 'Distinct'. It could be a popup
with the values of A and B with B as the default. The first time you enter a
particular State, the Distinct field gets A. Every other
time you enter that State, it gets B by default. Now you filter on
'Distinct field must contain A'. That would give one row for each
individual State.

[rmuetze]

Hello all,

sorry, for not beeing precise. My problem is comparable with DB100's states-example. In general, I do have a food intolerance and must take care about my nutrition. To keep the overview of my meals, I use handbase to collect what I eat. You can imagine that the data are growing very fast.

But collecting the data and gettting information out of the data are two different pair of shoes. In business developement this is comparable with data mining, but data mining might be oversized and is doing much more. So what I would like to get the information behind the data - may be reporting describes my problem a little bit more. A synchronisation of the mobile device handbase with the desktop could be an approach. On the desktop one have more possibilities - but than, I have to look into my desktop ... .

Back to my problem: What I would like to see is an overview by day or by meal. So I create different list views:

One list view with all data is like this (date, meal, what):
30.08.11, breakfast, rye bread
30.08.11, breakfast, marmelade
30.08.11, breakfast, boiled ham
31.08.11, supper, marmelade
31.08.11, supper, rice bread
31.08.11, breakfast, rye bread
31.08.11, breakfast, boiled ham
31.08.11, supper, rye bread
31.08.11, supper, marmelade

When I want to know my meals of a specific day, the date filter for views is fine (e.g. day = yesterday):
31.08.11, breakfast, rye bread
31.08.11, breakfast, boiled ham
31.08.11, supper, rye bread
31.08.11, supper, marmelade

But I also would like to know what I have eaten in the last days. So I would like to have a view, which shows the meals with "distinct "values" (e.g. distinct values last 2 days):

rye bread
marmelade
boiled ham
rice bread

By now, I added a checkbox "hideDistinct". When I open the listview "distinct values" and discover a double, then I check the hideDistinct field. The double is invisible than.

Especially the list view "distict values" could be performed and automated, if handbase supports an option "show distinct values" (on the basis of the option "Update view").

Hope this illustration brings a little bit more light into my situation. As mentioned before, my current solution is a checkbox "hideDistinct" which works fine by now.

Kind regards, rmuetze

[dhaupert]

Hi there,

Thanks for posting your question. I track my meals as well, all day, every day, using HanDBase. In my databases I have one database that has a record for each day. Using a link field I have a related table which is called Meals and so there are typically about 5-7 records for each day under that. Then under that table are Meal Items, where I list each element of each meal.

I track Protein, Fat, Carbs, Sugar and Calories for the day and so I additionally have some relationship fields to pull the total for the day and show it on the top level Meals database. This is something that at present only works on the iPhone and iPad versions of HanDBase. Normally when you grab a running total field via a relationship it doesn't recalculate the running total before grabbing but early this year I made the change to those versions as an experiment and it seems to be working out well. My database has every meal item from Jan 1st of this year and it is getting a little slow around now. I think the first 6 months were all almost instantaneous and at about 2500 meal items it began to start showing slight delays, but it's still quite usable for me.

I hope to bring this change over to Android soon!

I have done something in a few databases to be able to indicate when a new distinct value began. The method involved using a conditional and comparing the previous entry of a field to the current entry of the same field. If that field was the same, the output was 0, else it was 1. Then the conditional field itself can be used to show only distinct values. The trick in this case is to get the previous entry into the current record. If it's a numeric value, you can use a calculation and grab the prev value. But in your case, it's the meal name which is not numeric. One possibility here is that you can use the default value for a text field to take from the previous value. Set up a text field called prev meal, make it hidden and set to take its default from the previous meal value. You can now use this in comparisons, but note that on a new record, it's going to take from the last record currently visible in the filters. This can create issues if you are adding a record and have one of these views set- it's not going to get the true previous unfiltered value but the previous filtered one.

So assuming you have the filters set to none, this could work for you but require you to switch to this view before switching to the distinct one later.

That's about all I can come up with at this time!

[rmuetze]

Hello Dave,

thanks for taking the time to post on my request. I appreciate this and Iwould like to compliment your support

My handbase installation consists of several databases. Some databases work as a dictionary to look up often used values.

With regard to the meals db, I did some smaller changes in the database design. Each meal consists of several items. The meals db operates the item-names, the date, a note and the meal. So for one meal I have to enter several item-records. I do get the item-name from another database (my shopping-card, this database has the item-name and item-id). At the beginning the item-name was of the field type "linked". Unfortunately, I could not retrieve the item-name and item-id at the same time, so I revamped the design and changed the field type to "database lookup". With this change, I get both, the item-name and the item-id at the same time when I add an item-record. Both item values (name and id) are stored in the same group #.

Code: Select all

The design is now like this:

other db                    meals-db
__________                _____________________
|item-name|   <---------- |item-name, group #2 |
|item-id  |   <---------- |item-id, group #2   |
|..       |               |meal                |
-----------               |date                |
                          |note                |
                          |doublette           |
                          ---------------------


The next change is a new field "doublette". This field is calculated. The formula: add field item-id == prev field item-id. The result 1 indicates that the added record is a doublette when the item-id already exists, otherwise 0. Luckily, this works an ALL records, not only on the one in the list view and not only on the last prev record. If this is a bug, please keep it

With this setup, the meals db recognizes a double item-id automatically and I can easily filter the double item-names: My list view "distinct item-names" has a filter "doublette is between 0 and 0", which makes the records with doublette = 1 invisible.

Thanks again for your patience. Compared to my first solution this setup works much better for me

Best regards, rmuetze

[dhaupert]

Hi there,

You are a quick study! That's exactly what I had in mind for you so it seems like you have something you can use.