Relationship field weirdness - integer vs text

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

Relationship field weirdness - integer vs text

Postby ferrous » Wed Feb 20, 2013 6:37 am

I've been struggling with a set of databases for about three days now, and thought I must have been missing one tiny, obvious thing for it not to be working, but it turns out it's a weird bug or idiosyncrasy or something.

Here's the setup ...

Database A
Field type: Relationship
Related field in this database: Order #
Other database name: Database B
Field in other database: Order #
Show from other database: Calculate # of items

I had the almost exact same set up in Database Z and it was working perfectly, so I simply couldn't figure out where I'd gone wrong with Database A. I even recreated Database A from scratch to see if it was something I'd inadvertently missed. Still no dice.

In the end, I made a copy of Database Z to test it and changed the field names and types one at a time to figure out what was going on. It turns out, that it wasn't working because Database Z's equivalent field was text, while Database A's field in the same position and doing the same job was an integer. When I changed Database A's Order number to text, despite containers numerals, it suddenly worked!

Surely it's not intentional that it works this way.
Posts: 13
Joined: Mon Feb 18, 2013 11:02 pm

Re: Relationship field weirdness - integer vs text

Postby dhaupert » Thu Feb 21, 2013 12:19 pm

Hi there,

Thanks for your post. Indeed it is intentional from the development side. What is not intentional is that you had to struggle to find out about this limitation. Here's the actual details of why:

As you know working on mobile devices has it's tradeoffs- limited storage and speed are two of them when compared to the desktop computers.

HanDBase stores info in the most compressed format possible without going through actual processor intensive compression techniques (eg, zip files).
A number is stored in binary form whereas a text value is stored in ascii form.

The relationship field lookups involve many comparisons- we have to index the file to find all matching records, and the fastest way to perform this index is without having to convert the field types from their native stored format. So in this case an integer and text are stored differently and the comparisons will always fail.

I could change things to convert the field values to like formats, but this would slow down the field by a huge factor and you'd see performance hits for every relationship field. And this would not be acceptable.

In this case, there is a workaround, which is to convert your integer field to text using an additional field and then using that for relationships. Here's one way to do that:
Add a conditional field (which stores as text) with the condition if field x is equal to 0 output is [integer field] else output is [integer field]
It doesn't really matter what field x is as it will evaluate to one condition and the output is the same regardless. Then you can use this in your relationship field setup and it should then work.

Sorry I have not done a better job of documenting this!
Posts: 4113
Joined: Tue May 26, 2009 11:51 am

Re: Relationship field weirdness - integer vs text

Postby ferrous » Thu Feb 21, 2013 3:03 pm

That's a great workaround, Dave. I won't need it in this case, as the order number field isn't being used to calculate anything, but knowing that workaround will almost definitely be needed as I continue building this database. Thank you. :-)

As for documenting the way this works, it's entirely possible I just missed finding the documentation! I say we blame my 'baby brain' on this one (7-month-old baby makes mummy something something) and we're both off the hook. ;) :lol:
Posts: 13
Joined: Mon Feb 18, 2013 11:02 pm

Return to HanDBase for iPhone and iPod touch

Who is online

Users browsing this forum: No registered users and 1 guest