Base Record to Calc Question

[dreamquartz]

Hello All,

I can find all types of info about Calc -> Base, but not the other way around.
The Client runs a Split HSQLDB with, along other data, their Client info.
The Client is using a Calc sheet to calculate project costs.
The Calc Sheet has to have some identifiable info on it, and being saved based on that info.
That info is already in the DataBase.

Example:
Sheet
F1::FileName:: =CONCAT(F2," ",F3, " ",F4), to be used to identify the saved file
F2::Project #: DataBase vProjectNumber.ProjectNumber
F3::Company: BatasBase tCompany.CompanyName
F4::Project Name: Description of the project, either from DataBase of as manual input in the Calc Sheet*
F5::Location: DataBase vAddress.Address
F6::Date: DataBase tServiceUsage.DateOfProject

*: There are discussions ongoing to either use information from the DataBase or use the Cell F4 itself to enter some description. Therefore both options are still open.
NOTE: vAddress indicates a View, and tCompany indicates a Table.

Whatever I try, I cannot find a method to dynamically select the ProjectNumber, that needs to be in Cell F2, to allow the other information to be filled out automatically.
I can create a Form, with the following:

Code: Select all

SELECT
	 "vProjectNumber"."ProjectNumber",
	 "tCompany"."CompanyName",
	 "vAddress"."Address",
	 TO_CHAR( "tProjectDateTime"."DateOfProjectStart", 'MM/DD' ) ||
		 CASE WHEN "tProjectDateTime"."DateOfProjectFinish" IS NULL
			 THEN
				 TO_CHAR( "tProjectDateTime"."DateOfProjectStart", '/YY' )
			 ELSE
				 CASE WHEN TO_CHAR( "tProjectDateTime"."DateOfProjectStart", 'MM' ) < TO_CHAR( "tProjectDateTime"."DateOfProjectFinish", 'MM' )
					 THEN TO_CHAR( "tProjectDateTime"."DateOfProjectFinish", '-MM/DD/YY' )
					 ELSE TO_CHAR( "tProjectDateTime"."DateOfProjectFinish", '-DD/YY' )
				 END
		 END "ProjectDate(s)",
	 "tNotes"."Notes"
 FROM
	 "tNotes"
		 RIGHT OUTER JOIN "vProjectNumber" ON
			 "tNotes"."FKProjectNumber" = "vProjectNumber"."ProjectNumberID",
	 "tProjectDateTime",
	 "tServiceUsage",
	 "vAddress",
	 "tCompany"
 WHERE
	 "tProjectDateTime"."FKServiceUsageID" = "vProjectNumber"."ServiceUsageID" AND
	 "tProjectDateTime"."FKServiceUsageID" = "tServiceUsage"."ServiceUsageID" AND
	 "vAddress"."AddressID" = "tServiceUsage"."FKAddressID" AND
	 "tCompany"."CompanyID" = "vAddress"."FKCompanyID"
 ORDER BY
	 "tProjectDateTime"."TimeStamp" DESC

and create all the fields, but cannot get the info from the specific field in the specific Cell.

It would be so handy if the following is possible: Add Text Box, link it to a specific Cell and Source, as linking a Cell is possible under Properties: Text Box. The option is however 'Greyed' out.

Is there a way to accomplish the transfer dynamically, because the Calculus is a dynamic Template.

Kind regards,

Dream

[Lupp]

(I'm not a user of Base, but...)
You might better search the Base forum.
What about search.php?keywords=query+result+to+cal ... %5B0%5D=13 ?

[Villeroy]

Base
1. filter record storing some ID number of the desired record at a distinct filter ID.
2. query selecting the right data from various tables based on the ID number stored in the filter table.
3. listbox query selecting some visible string and the ID number to be stored in the filter record.

Calc
4. database range linked to query #2.
5. form on the sheet linked to filter record #1.
6. listbox on the form linked to listbox query #3.
7. button to cancel listbox input.
8. button storing the listbox input to the filter record.
9. macro button refreshing the database range #4 pointing to
10. macro with

Code: Select all

ThisComponent.DatabaseRanges.getByName("Import1").refresh()

Instead of the macro button #9 you may bind the form's event "After record action" to the macro.

[Villeroy]

Save both docs to the same trusted location.
Open the spreadsheet. A macro will register the database.
Reload the spreadsheet one more time.
Pick a person from the list box and confirm "OK {Enter}".