OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Linking AutoComplete to an OOo Base Database

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
sdritchey
General User
General User


Joined: 27 Oct 2005
Posts: 23

PostPosted: Tue Sep 21, 2010 2:46 pm    Post subject: Linking AutoComplete to an OOo Base Database Reply with quote

Hi,

I have a spreadsheet where I'd like to type in a few letters into a cell and have Calc lookup the potential complete entries from a field in a Base database. I would also like to have other fields from the chosen database record to fill in automatically in other cells in the same row in the Calc spreadsheet. I've looked around and played with the data sources, but can't really get this working. Anyone know how to do this?

Thanks,
Steve
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Sep 21, 2010 3:39 pm    Post subject: Reply with quote

http://www.oooforum.org/forum/viewtopic.phtml?p=392627
http://user.services.openoffice.org/en/forum/viewtopic.php?t=21099&p=96427#p96427
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
sdritchey
General User
General User


Joined: 27 Oct 2005
Posts: 23

PostPosted: Wed Sep 22, 2010 10:33 am    Post subject: Reply with quote

I looked at both the posts and played with the downloads. Just to confirm before I dive in, it looks like the best way to accomplish the above is to use DataPilot to pull data from my database to a sheet in my spreadsheet, then reference this sheet for the autocomplete values. Correct?

Thanks for helping on both my posts!

Steve
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Sep 22, 2010 10:51 am    Post subject: Reply with quote

A combo box with auto-complete writes some text into a filter table.

The following query is the data source of the pivot or other import ranges:
Code:
SELECT D.A, D.B, D.C, D.D, D."Whatever" FROM "Your Data" AS D, "Filter Table"AS F
WHERE (F."SearchText"=D."SearchField" AND F.ID=0) OR F."SearchText" IS NULL


The pivot is linked to that database query which reads the filter criteria from field "SearchText" in one particular record (F.ID=0) in table "Filter Table". If "Search Text" is NULL, all the rows are returned.
The combo box is bound to field "Search Text"

The combo's form is bound to:
SELECT "Search Text", ID FROM "Filter Table" WHERE ID=0
so it will write its value into one particular cell.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
sdritchey
General User
General User


Joined: 27 Oct 2005
Posts: 23

PostPosted: Thu Sep 30, 2010 11:22 am    Post subject: Reply with quote

Well I never could get Villeroy's solution to work, so here's my solution if anyone else is trying to accomplish the same thing.

I ended up making a query in Base with the fields I wanted, then by using Data Sources in Calc dragged and dropped the table into a separate sheet. I then referenced this table with the LOOKUP function.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group