| View previous topic :: View next topic |
| Author |
Message |
sdritchey
General User
Joined: 27 Oct 2005
Posts: 23
|
 Posted: Tue Sep 21, 2010 2:46 pm Post subject: Linking AutoComplete to an OOo Base Database |
 |
|
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 |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
sdritchey
General User
Joined: 27 Oct 2005
Posts: 23
|
| Posted: Wed Sep 22, 2010 10:33 am Post subject: |
|
|
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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Sep 22, 2010 10:51 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
sdritchey
General User
Joined: 27 Oct 2005
Posts: 23
|
| Posted: Thu Sep 30, 2010 11:22 am Post subject: |
|
|
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 |
|
|
|
