Merging data to one database

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

Merging data to one database

Postby nigelp » Wed Jul 15, 2009 7:06 am

First off really enjoying this appliction but as I discover more I seem to need to know more!!

Thanks to previous support I have started creating a number of lists to plan expedition equipment and food. Basically I have two seperate databases one for equipment and one for food. 
'Equipment' i use to list items to pack, their weight and each item can be checked returning the total weight for items packed. 

The food database works in a similar way but calculates the number of portions for checked items as well as the amount of fuel required and portion amounts etc.  

Is there a way to create a new database that will bring into it some of the information from the other two databases so I can just see say the total weight of the packed bag? Amount of fuel required? Etc...

Or do I have to start from scratch and  start with the database that the information will be displayed in and add other bits on? Would it be like 'packing listpro' with it all in one database and if so which tutorials do I need to read?

Sorry lots of questions!!  Nigel
nigelp
 
Posts: 38
Joined: Fri Jul 10, 2009 3:51 am

Re: Merging data to one database

Postby dhaupert » Wed Jul 15, 2009 9:40 am

Hi Nigel,

Sounds like an exciting trip is being planned! ;)

I would think you could still add to the existing tables you made. The relationship field would be your best bet to pull values from the other database tables. The problem/work involved is that there needs to be a way to relate each record in the existing tables to this master record. I would think that perhaps a trips database would be in order. In this database you'd have:

trip_id: A text field where you give each expedition a name.

You would also add a similar field to the other tables and set the value to the same value for matching trips.

Then in the trip database, you'd be able to add:
trip_total_fuel: a relationshp where you link the fuel database's trip id to this trip id and display the total fuel (calculated running total)'s last record.
trip_total_bag_weight: a relationship where you link the equipment database's new trip_id field to this table's trip_id field and take the running total of the weight.

Once this is set up, you can then use the trip database as a launching point to add new Equipment items, fuel records, etc. When you do that, the trip_id is auto filled on the related database. And your totals will be reflected when you return to this table after making the edits.

As an aside, I'd love to see you post this database to the Gallery when you're finished with it- it seems far more advanced than the packing databases on there, and I'm sure would interest some others as well!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: Merging data to one database

Postby nigelp » Wed Jul 15, 2009 10:22 am

Excellent I will have a play about and see how it works. I may have a rad of the Manuel again now I khow what it is I am looking for. When the thing is complete I would be only too pleased to post it up on the site. There are a number of 'light weight' back packers who may find this usual. I do!
nigelp
 
Posts: 38
Joined: Fri Jul 10, 2009 3:51 am

Re: Merging data to one database

Postby nigelp » Thu Jul 16, 2009 10:39 am

dhaupert wrote:Hi Nigel, Sounds like an exciting trip is being planned! ;)

Hi I am always planning trips!

dhaupert wrote:trips database would be in order. In this database you'd have: trip_id: A text field where you give each expedition a name.


Ok I created a new database which I called 'Trips' The first field I added was called 'Trip ID' and the type was text?

dhaupert wrote: trip_id: A text field where you give each expedition a name. You would also add a similar field to the other tables and set the value to the same value for matching trips.


For the first trip I decided to call it 'DM' for Dartmoor I added a new field to the other two databases also called Trip ID and by each item of equipment packed and by each food item to be packed filled the 'Trip ID' field with the text 'DM'?

dhaupert wrote:Then in the trip database, you'd be able to add:
trip_total_fuel: a relationshp where you link the fuel database's trip id to this trip id and display the total fuel (calculated running total)'s last record.
trip_total_bag_weight: a relationship where you link the equipment database's new trip_id field to this table's trip_id field and take the running total of the weight.


This was the bit that lost me slightly ;-)
I added a new field called Total Fuel - type is 'relationship' what i cant understand is how, in this field to both link the trip ID and establish a relationship to bring the total fuel last record over to it (also for bag weight)?

As an aside is it possible to 'copy' or 'save as' a database so that when I plan my next trip I can use the entries from the previous trip but make amendments to it?

Thanks Nigel
nigelp
 
Posts: 38
Joined: Fri Jul 10, 2009 3:51 am

Re: Merging data to one database

Postby nigelp » Sun Jul 19, 2009 2:19 pm

Bump!!
nigelp
 
Posts: 38
Joined: Fri Jul 10, 2009 3:51 am

Re: Merging data to one database

Postby dhaupert » Wed Jul 22, 2009 10:43 am

This was the bit that lost me slightly ;-)
I added a new field called Total Fuel - type is 'relationship' what i cant understand is how, in this field to both link the trip ID and establish a relationship to bring the total fuel last record over to it (also for bag weight)?

As an aside is it possible to 'copy' or 'save as' a database so that when I plan my next trip I can use the entries from the previous trip but make amendments to it?

Thanks Nigel


Apologies for the delayed response- was away on vacation the last few days!

In the trip database, your relationship field will have the following settings:
Related Field in this database- point to the Trip ID field you created in the Trip database.
Other database name- point to the database where you're logging the fuel
Field in other database- point to the Trip ID field you created in the fuel logging database
Show from other database- point to the total fuel calculated field in the fuel logging database
From which record- choose "last matching record' so that it takes the last record's total (which is the value of the running total)

I hope this makes sense. If not, I'd suggest one of the tutorials on using relationship fields to help you understand the field type.

In regards to a copy or save as option on the iPhone- there is not yet such an option. You can copy the template/structure of the database by selecting the + button to add a new database and choosing 'Choose Existing Template'. But this does not copy any data, just the structure and popup values. On the desktop, there is a File, Save As option to do what you are asking. Perhaps eventually I will add a duplicate database option in the Choose Database screen if there is enough demand for that feature!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: Merging data to one database

Postby nigelp » Wed Jul 22, 2009 1:40 pm

Hi Dave

Thanks that all worked a treat and can now see an overview of the next planned trip. I am using Handbase desktop 4 for Mac and can not see the 'save as' option only an option to save the currently open database?

Cheers Nigel
nigelp
 
Posts: 38
Joined: Fri Jul 10, 2009 3:51 am


Return to HanDBase for iPhone and iPod touch

Who is online

Users browsing this forum: No registered users and 1 guest