How do I merge cells from a cell range(row) on Base

[ehesh]

I have a macro which pretty much allows me to copy a range to a single cell on Calc, which I want to use on Base. But I do not know how to apply it towards a table on Base. I need help knowing where the table name goes. could it be possible to have the macro execute automatically? by default?

The macro is pretty much like this

Sub Main

End Sub

sub copyrangetosinglecell()
dim document as object dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher =
createUnoService(“com.sun.star.frame.DispatchHelper”)
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = “ToPoint”
args1(0).Value = “$B$5:$E$5”
dispatcher.executeDispatch(document,
“.uno:GoToCell”, “”, 0, args1())

   dispatcher.executeDispatch(document,

“.uno:Copy”, “”, 0, Array())

dim args3(0) as new
com.sun.star.beans.PropertyValue
args3(0).Name = “ToPoint”
args3(0).Value = “$I$3”

dispatcher.executeDispatch(document,
“.uno:GoToCell”, “”, 0, args3())
dispatcher.executeDispatch(document,
“.uno:SetInputMode”, “”, 0, Array())

dispatcher.executeDispatch(document,
“.uno:Paste”, “”, 0, Array())

dim args5(0) as new
com.sun.star.beans.PropertyValue
args5(0).Name = “ToPoint”
args5(0).Value = “$C$3”

dispatcher.executeDispatch(document,
“.uno:GoToCell”, “”, 0, args5())

end sub

[Ratslinger]

Hello,

It is much more than knowing where the table name goes.

When accessing Base from Calc via a macro, you need to establish a connection (usually via a registered name), create the appropriate SQL to Update or Insert the record in the table chosen and use the information from Calc within the statement for updating. Finally you execute the SQL to actually insert the data.

Sample code (updating existing record) can be found in my answer on this post → Date fields are driving me mad

Edit:

Based upon the new information, you can simply add a sub form to the entry form. The sub form is based upon a SQL statement concatenating the fields. The format depends upon what you are looking for. The attached sample provides two different results (each on its’ own sub form). The first is each field connected with a single space between each field. The second uses a carriage return between each field. The resulting concatenated field is only available once the record has been entered, go to a different record and then return to that record. That does save from writing code to generate the results (always avoid macros whenever possible).

Sample — DisplayConcatonatedFields.odb

[ehesh]

but there are no letters for the rows on the table. I am no longer using calc perse, I am trying to use a table from the database itself. dunno if that makes sense?

[Ratslinger]

@ehesh So are you saying this has nothing to do with Calc or the process you have posted? Are you stating you wish to merge data from multiple fields in a record to another field within the same record? If so, it is a waste. You can always obtain that result via a Query. You should not duplicate storage of information within a database. Relational DB’s were meant to eliminate that duplication.

[ehesh]

exactly, nothing to do with calc. the macro I included is one that I used in calc, I want to make a version of this macro but for Base. The reason I want to do this is that I want to have the cell range copied in the clipboard since I must paste the range on different other programs. I don’t actually need to have the entire to be on another cell, but my boss does want the format to already be in a single cell. what need myself is to be ready to just pasate the range after the entry is saved

[Ratslinger]

OK, starting to get to the actual question. Wish you has better knowledge of terminology. There are no cells but rather fields in a record. You don’t need any macro based on what I get from the comment. However, don’t know what you want the output to look like. Is it:

Field 1 = `Hello`; Field 2 = `There`; Field 3 = `Sir`

With result =

HelloThereSir

or

Hello There Sir

or what?

[ehesh]

I just started using Base a week ago, I wish I knew a bit more too,
And yes that is what I want it to output, but as

Hello
There
Sir

[Ratslinger]

@ehesh First you may want to peruse the LO documentation → LibreOffice Base Handbook. This will help in concepts & terminology.

Secondly, not waiting for your answer, I had already produced a sample yesterday which already contains the format you just mentioned. Please see edit in my answer.

[ehesh]

Took me a while to understand how to set it up with my own fields, but it is very interesting. Thank you very much.

"FieldA" || CHAR( 13 ) || "FieldB" || CHAR( 13 ) || "FieldC" || CHAR( 13 ) || "FieldD"

Now, I will try to see how I can have SQL command populate a single field of a table, but I think that si more for another question. I’ll try to find a way to solve this through the weekend

Again, Thanks

[Ratslinger]

@ehesh The SQL you have used is valid but FYI it can differ depending upon the database used. Some use concat vs ||. Also, SQL to place this in another field is not the way to go. Either use an Update statement or a specific macro must be written to place this in a record field. Again, you really shouldn’t duplicate data in a relational database. What you have stored can produce the result you want when needed.

[trueriver]

Agree with @ratslinger that data duplication is to be avoided, but so is code duplication. I also suggest you don’t write this into a macro at all.

The best way to do this is to create a view with an extra calculated field.

A view is a pretend table that contains a mix of original data and calculated values

In the tables section, click on create view, and add your base table to the view. Click on the * to include all the original columns (fields). Then in the next column of the view put the expression as the value, and give it the alias (couple of rows down) of allFields. Save.

Now select from that view as if it was a table with the column “allFields” as well as the original data.

All the data is non-duplicated.

All the code is non-duplicated, but is run every time you access a value from the calculated column.

You can even insert new data into the view and the calculated value automatically adjusts. It is a read-only field of course, so to change the value of allFields you need to change the relevant value of FieldB (say) in the same row.

A view is probably the most powerful new idea you will meet in moving from spreadsheet to database. There is a lot more you can do than just calculate fields from a single table, but this will do to get you started.

If you base several forms on a view and later want to change the calculation, you just do it the once. The form “thinks” it is a table anyway, so does not need to know any of this kind of code.

Hope that helps.

Once you get used to using views, you will wonder how you ever managed without them…

[Ratslinger]

@trueriver I believe you are a bit misleading here. Views are great in many different ways. However, you cannot add data to a view nor change the data in a view. The data must be added or modified in the original table which in turn displays the additions/modifications in the view.

Views come in handy to allow others to see only what they need to see. They are also great where repetitive creation of complex SQL displays are needed.

[ehesh]

I had no idea on what views were. This is amazing, combining both answers actually gives me my desired answer. But I have to credit Ratslinger for his extended support in addition to the sample file he included.

[ehesh]

I had no idea on what views were. This is amazing, combining both answers actually gives me my desired answer. But I have to credit Ratslinger for his extended support in addition to the sample file he included.