Self referencing relationships - the details

Discussion of the version of HanDBase that runs on the iPhone and iPod touch devices. This includes the synchronization conduits as well.

Self referencing relationships - the details

Postby jag » Sun Nov 20, 2011 1:18 am

For the benefit of this community, I am posting an email conversation I had with Dave that will provide some insight into this "feature":


Code: Select all
From: Jag Robles
To: Dave Haupert
Sent: Sunday, November 13, 2011 8:40 AM
Subject: Database NoDupe

Please find database NoDupe attached.

This is in relation to "HanDBase 4.8.1 Known Bugs"

I created this DB today to help someone in the forum and in doing so discovered that DB does not work in iPhone, but works perfectly in Palm.

I suspect self Relationships no longer work :-( Which I use a lot!

Thanks for your time!

Best wishes,
Jonathan

- - - - - - - - - - - - - - - - - - - -

From: Dave Haupert
To: Jag Robles
Sent: On 15/11/2011, at 0:24
Subject: Re: Database NoDupe

Hi Jonathan,

Thanks for sending the database.  I believe the reason why this is different from Palm to other platforms is that we used the native PDB format of the Palm device.  To open a database on the Palm took up zero memory- it was supported via the native calls to do so.  On other platforms, we have to read it into memory, and thus it gives a memory hit for each lookup.  Though the program doesn't actually read the entire database into memory for each open (it caches a single copy) it does have many other variables to govern it.  Since a relationship to the same database can cause a loop (the referenced database will look at itself), it will soon run out of memory.   That is likely what is happening here, but I'm going to take a look and see for sure. 
I'll get back to you on this.

Blessings,
Dave Haupert

- - - - - - - - - - - - - - - - - - - -

From: Jag Robles
To: Dave Haupert
Sent: On Nov 14, 2011, at 4:54 PM
Subject: Re: Database NoDupe

Dear Dave,

Thanks for looking into this. When you go to the All view, everything works as it should. It's only when I go into the Duped view that it does not work as it does on Palm.

I have other large DBs that don't work at all, that use this self relationship method, that I wish so much to get working. Hope so much you can find a way to fix this.

Best regards,
Jag

- - - - - - - - - - - - - - - - - - - -

From: Dave Haupert
To: Jag Robles
Sent: On Tue, Nov 15, 2011 at 10:20
Subject: Re: Database NoDupe

Can you explain to me the process you are hoping for with the relationship and the calculation based off it?  I'm not seeing how this would generate a no dupe value when two sets of consecutive records have the same count. For example:

Joe
Joe
Joe
Mary
Mary
Mary

It would seem that in all records except the first Joe the previous count would equal 3.

Perhaps I am misunderstanding something here- it has been a long day!

Also, please note that unlike older versions of handbase, the iOS versions recalculate all records that match before grabbing a value. This was a HIGHLY requested about a year back and it took me quite a while to make that happen. This may be causing an incorrect assumption on your about the calculated value.

Blessings,
Dave Haupert

- - - - - - - - - - - - - - - - - - - -

From: Jag Robles
To: Dave Haupert
Sent: On Nov 14, 2011, at 7:42 PM
Subject: Re: Database NoDupe

Actually all "Rel" fields will show the total of any name because I am showing Last Matching Record.

In the NoDupe.PDB database, your sample data would look like this (in the "All" View):

+------+------+----+-----+
|NAME  |COUNT |REL |DUPE |
+------+------+----+-----+
|Joe   |1     |3   |0    |
|Joe   |2     |3   |1    |
|Joe   |3     |3   |1    |
|Mary  |1     |3   |0    |
|Mary  |2     |3   |1    |
|Mary  |3     |3   |1    |
+------+------+----+-----+

By filtering DUPE (which should happen with the "Duped" View), you should be able to delete all duped records.

This is such a powerful function that could be used to provide so many solutions to people on the forum. I have implemented these in Palm and it saddens me that it is not working on the much more powerful iPhone :-(

Hope you can figure it out.

- - - - - - - - - - - - - - - - - - - -

From: Dave Haupert
To: Jag Robles
Sent: On Tue, Nov 15, 2011 at 12:12
Subject: Re: Database NoDupe

I am referring to the no dupe calculation here. Ignore the output you see in the actual database but rather explain to me how the calculation would set dupe to 1 on the fourth record? It's rel is 3 and the prev rel field is also 3, so I don't see how or why it would work in that case.  Please explain.

I agree that there could be uses for a self referential lookup but I need to first fully understand this setup before I can debug.

Blessings,
Dave Haupert

- - - - - - - - - - - - - - - - - - - -

From: Jag Robles
To: Dave Haupert
Sent: Monday, November 14, 2011 8:56 PM
Subject: Re: Database NoDupe

As you add a new (duplicate) name, Dupe will not be 1. The Rel will also have the wrong count (should be 4 it it's value was being obtained in real time). It is only when you save the record that the Dupe calculation works. I imagine, that the order I have setup the fields is very important, as well as the records' order in the View, so when Dupe calculates if the previous Rel is equal or not to current Rel, it can actually only see the current Name only. So the most important calculation is the first one and because the previous record would have incorrect name, it will always provide a 0 in the logical test.

I hope I have explained myself enough for you to see why I think this works. I would love to be able to see a flow chart of how HanDBase actually "flows" and I may be able to better help you.

As I try to understand why the "Duped" View does not work, I can only imagine that it is because of the filtering being done in real time, instead of doing the filtering after all records have been calculated - which should, IMHO, be the proper way to accurately filter; specifically in iPhone.

"note that unlike older versions of handbase, the iOS versions recalculate all records that match before grabbing a value" - this probably why the "All" View values are all so accurate. My test on Palm is inaccurate, but still works; allows me to filter out duplicates.

Thank you for making the time to look into this further.
If I can be of more help, please don't hesitate to let me know.

Best regards,
Jag

- - - - - - - - - - - - - - - - - - - -

From: Dave Haupert
To: Jag Robles
Sent: On Wed, Nov 16, 2011 at 00:26
Subject: Re: Database NoDupe

Hi Jonathan,

I'm still not seeing why this formula would work in theory, which is something I'd have to understand to be able to even begin finding out if it's going to be possible to support it.  Let's go back to the chart and I'm going to add in a value P5 (which is part of your formula to get the previous rel value and compare it to the current one):

+------+------+----+---------+-----+
|NAME  |COUNT |REL |Prev Rel |DUPE |
+------+------+----+---------+-----+
|Joe   |1     |3   |  0      | 0   |
|Joe   |2     |3   |  3      | 1   |
|Joe   |3     |3   |  3      | 1   |
|Mary  |1     |3   |  3      | 1   |
|Mary  |2     |3   |  3      | 1   |
|Mary  |3     |3   |  3      | 1   |
+------+------+----+---------+-----+

You will note that the prev rel column for the first Mary record would be 3 because the previous record's REL column being 3.  This means that the DUPE field, which you have set up as P5==F5 or Prev Rel is equal to Rel would be 1, not a zero.

I understand this is not what you're seeing in the program, but we have to take it to it's logical conclusion and understand the formula before we can begin to adjust the way HanDBase behaves.  Just because the Palm version handled it the way you needed, does not make it that the Palm version was actually right.  It may have been a red herring that it worked for you- perhaps you never had a list of records where the count would be equal for two sets of records in a row.   

In regards to this statement:
As I try to understand why the "Duped" View does not work, I can only imagine that it is because of the filtering being done in real time, instead of doing the filtering after all records have been calculated - which should, IMHO, be the proper way to accurately filter; specifically in iPhone.

Sometimes filters depend on a calculation, and sometimes a calculation depends on the filtering.  It's near impossible for the program to decipher which is which.  What is different for sure from the Palm version is that in the old days when I did a grab of a relationship value from another field, I did NOT recalculate the values.  So for example, if you had a database with a Previous field + 1 calculation (like your count field), you may have a database like this:

Name   P2+1
Joe      1
Joe      2
Joe      3
Mary     4
Mary     5
Mary     6

If you opened the database directly, the P2+1 calculation would be recalculated and the values 1-6 would be filled in like above.  Now imagine you have a database which pulls the last record's count (P2+1).  Something like this:

Name   Number of records
Joe      3
Mary     6

This is obviously incorrect, yet this is exactly what the program would return because for performance reasons I did not recalculate the values during each relationship lookup. 

In HanDBase for iPhone and the iPad version as well, I finally do that recalculation.  So you would instead get:

Name  Number of records
Joe     3
Mary    3

This is something I have had reported as a bug for many years, but it was always listed as a limitation of the Palm, Windows, etc versions.  I would offer as a workaround that you should tap the relationship field in the latter database so that the recalc would happen and the right value would be brought back, but often customers did not like this, for good reason. 

But this new behavior will definitely give you a different result in key areas like this- the problem is that it's not wrong in doing so, it's just an adjustment.  Unfortunately where this falls short is that the relationship field to itself means that when evaluating the lookup, a new set of calcs are being done.  And since that new set of calcs may use a relationship like your example, that's where the problem comes in- it is recursion and the only reason a crash does not happen is that there is some protection built into the program to only recurse a max number of levels deep.  Despite that, you'll see it's very slow to run your test database, and that's the reason why.  It is also the reason why the results don't return the way you'd expect them to. 

In a posting on the forums a while back I offered a creative solution to only show unique records for a given field.  It did not involve relationships that I can recall.  I will have to search and dig that up so you can see a different way to accomplish this.
 
Blessings,
Dave Haupert

- - - - - - - - - - - - - - - - - - - -

From: Jag Robles
To: Dave Haupert
Sent: On Nov 16, 2011, at 4:09 AM
Subject: Database NoDupe

Dear Dave,

Thanks for the details.

Unfortunately I cannot see the calculations as you do. When a record starts a relationship calculation, it's like a filter. When you filter the name JOE,  then use a PREV REL, that PREV REL should be within the scope of the already filtered records, JOE. So the DUPE calculation will always show you a 0 for the first unique NAME because the PREV REL is only existent after the second repeated name. I am not sure what happens programmatically when you PREV REL has nothing to look up, HanDBase must see that as an error, out of bound, I don't know, but I am glad it spits out 0 in the calculation.

When you compare how Filters work with calculations, the same takes place. So the REL acts like a filter.

The Palm and iPhone both provide a good result for the PREV REL, it is only when I try to use the View DUPED, which has a filter looking for DUPE = 1, when the iPhone hangs :-/ Why? The real time recalc? I still don't understand why though :-(

Hope I have not repeated myself so you can try and figure out how to make the filter work.

I am also interested to see how else you would filter duplicates, but would love for the above to work because I have several more applications for it.

Best regards,
Jonathan

- - - - - - - - - - - - - - - - - - - -

From: Dave Haupert
To: Jag Robles
Subject: Database NoDupe

The prev rel that is being referred to here needs to be clarified.  Within a calculation a previous record reference goes to the previous record within the filters and if there is match, evaluates that field.  In the case where there is no previous record, a 0 is always returned (as you saw).  But in the case when you are looking at the all records where there is no filtering and in the case of the example we are speaking about with the table typed out in previous emails, there is no filter set, so the previous record for the first Mary would be the Joseph record, and hence it's relationship value.  That's why the calculation doesn't make sense from a theoretical standpoint.  As I mentioned the Palm and Windows versions had some bugs (and still do) from this standpoint as when they did a relational lookup, they never did a recalc. So it was quite possible for you to see a result that you wanted when in reality it was due to a bug or limitation of the program!

When you factor in the filtering going on, things get worse- if you have a filter that is based on a calculation, which then determines the output of the filter, you're going to get an undetermined response.  Kind of like this setup, where the record is shown only if the value after calculation is 1, but the calculation is dependent on what record precedes it within the filters. 

I hope this makes sense. I will try to dig up that forum posting when I'm able to- right now I'm actually using my iPad for emails as my computer is quite busy doing something!

- - - - - - - - - - - - - - - - - - - -
Attachments
NoDupe.zip
Use at own risk!
(2.89 KiB) Downloaded 49 times
jag
 
Posts: 83
Joined: Wed May 20, 2009 10:29 pm

Return to HanDBase for iPhone and iPod touch

Who is online

Users browsing this forum: No registered users and 1 guest