Ask Your Question

Dycius's profile - activity

2019-05-14 14:58:28 +0200 received badge  Taxonomist
2018-11-05 10:26:37 +0200 received badge  Famous Question (source)
2016-03-07 01:45:56 +0200 marked best answer LO Base 3.4: Display all records from a table on separate pages?

Hi all,

I know LO 3.4 is old, but my work IT department refuses to upgrade. :( Anyways...

I hope I can explain this well. I have a database with one table. The table has multiple fields (name, description, point1, point2.... and so on). What I want to do is print each record in this table but using one page per record. The page is set up with the text in specific locations with lines and graphics. The table will have hundreds of records that will change. However, each record needs to be displayed in the same manner, so I want a report that will generate all the pages where I click print and all will be printed. I have created a report in Base but it only displays one record at a time and I need to use the next and previous arrows to change records. This would be time consuming to click next and print for each one. In this version of Base, there is no Report Builder that I can find. I only had the option to create a Report from wizard, so I did that and deleted everything and rebuilt it in the order I wanted. The wizard did have it so all the fields were displayed in a table. However, that is not how I want it to look. I need to dynamically make all pages based on the one master template.

Also, I'm looking at wanting to filter the data in the future. There is a date field for when a record is changed. Would it be possible to only make a report with records after manually given date so only those recently changed are displayed and printed?

I hope this is clear, if not let me know and I will try to mock something up.

Jon

2016-03-07 01:40:11 +0200 marked best answer LO Base: Add a record with a default value in a many to many relationship?

Hi all,

I will post the tables I am referring to in SQL form so you know what they are like.

CREATE CACHED TABLE "Store"(
  "PK_Store_Num" SMALLINT NOT NULL PRIMARY KEY,
  "Branch" VARCHAR(20) NOT NULL,
  "CityTown" VARCHAR(50) NOT NULL
)
CREATE CACHED TABLE "Employee"(
  "PK_Employee_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
  "FirstName" VARCHAR(100),
  "LastName" VARCHAR(100)
)
CREATE CACHED TABLE "StoreEmployee"(
  "FK_Store_Num" SMALLINT NOT NULL,
  "FK_Employee_ID" INTEGER NOT NULL,
  PRIMARY KEY("FK_Store_Num","FK_Employee_ID"),
  CONSTRAINT SYS_FK_59 FOREIGN KEY("FK_Store_Num") REFERENCES "Store"("PK_Store_Num"),
  CONSTRAINT SYS_FK_63 FOREIGN KEY("FK_Employee_ID") REFERENCES "Employee"("PK_Employee_ID")
)

As you can see it's a simple structure to practice on before working on the main database.

There is a predefined list of stores and shouldn't be editable. Ideally, I would like to select a store, see all the employees for that store and then add new ones. I couldn't figure out how to make that work right, as in make a new employee and at the same time add it to the store table as well.

The best I can come up with from the examples I read is to make the employee table the main form, then the link table the sub form with the store as a drop down list. It works, however, when adding a new employee, the record needs to be entered, and then selected again and then the store selected from the drop down list. This is way too cumbersome if a) there are a lot of employees to add and b) if the store list is long.

Is there a way to have a store selected, and then a button that adds the employee record to the employee table and at the same time adding the correct linking IDs to the link table?

Also, I need to do my original idea of filtering the employee table so that only records from the preselected store are shown.

I can use macros if need be.

Thanks, Jon

2016-03-05 22:04:31 +0200 received badge  Famous Question (source)
2016-03-05 18:32:46 +0200 received badge  Notable Question (source)
2016-03-05 18:32:46 +0200 received badge  Popular Question (source)
2016-02-21 14:58:07 +0200 marked best answer LO Base: What is wrong with my Insert Into Statement?

Hi all,

I'm trying to do an INSERT INTO statement in a macro, however I get the following error message when the SQL is executed.

image Error Message

I will paste the whole code so you can see what's going on. It doesn't have everything yet which is why there are extra variables.

Sub Insert_Row_Days_Employees (oEvent As Object)
    Dim oDoc AS OBJECT
    Dim oDrawpage AS OBJECT
    Dim oFormStore AS OBJECT
    Dim oFormFiscalWeek AS OBJECT 
    Dim oFormFiscalDays AS OBJECT
    Dim oFormRota AS OBJECT
    Dim iStoreID AS INTEGER
    Dim iWeekID AS LONG
    Dim dSun AS DATE
    Dim dMon AS DATE
    Dim dTue AS DATE
    Dim dWed AS DATE
    Dim dThu AS DATE
    Dim dFri AS DATE
    Dim dSat AS DATE
    Dim oStatement as object
    Dim sSQL AS STRING
    Dim sColumns AS STRING
    Dim sValues AS STRING

    oDoc = thisComponent
    oDrawpage = oDoc.Drawpage
    oFormStore = oDrawpage.forms.getByName("Store")
    oFormFiscalWeek = oFormStore.getByName("FiscalWeek")
    oFormFiscalDays = oFormFiscalWeek.getByName("FiscalDays")
    oFormRota = oFormFiscalWeek.getByName("Rota")

    oStatement = oFormFiscalWeek.ActiveConnection.createStatement()

    oFormFiscalWeek.UpdateRow
    iStoreID = oFormStore.Columns.GetByName("PK_S_Num").Value
    iWeekID = oFormFiscalWeek.Columns.GetByName("PK_FW_ID").Value
    dSun = CDateFromISO(oFormFiscalWeek.GetByName("datActualStartDate").CurrentValue)

    dMon = DateAdd("d",1,dSun)
    dTue = DateAdd("d",2,dSun)
    dWed = DateAdd("d",3,dSun)
    dThu = DateAdd("d",4,dSun)
    dFri = DateAdd("d",5,dSun)
    dSat = DateAdd("d",6,dSun)

    sColumns = ""
    sColumns = sColumns & """FK_FD_FW_ID"""
    sColumns = sColumns & ", ""Sun_Actual_Date"""
    sColumns = sColumns & ", ""Mon_Actual_Date"""
    sColumns = sColumns & ", ""Tue_Actual_Date"""
    sColumns = sColumns & ", ""Wed_Actual_Date"""
    sColumns = sColumns & ", ""Thu_Actual_Date"""
    sColumns = sColumns & ", ""Fri_Actual_Date"""
    sColumns = sColumns & ", ""Sat_Actual_Date"""

    sValues = ""
    sValues = sValues & iWeekID
    sValues = sValues & ", {D '" & dSun & "'}"
    sValues = sValues & ", {D '" & dMon & "'}"
    sValues = sValues & ", {D '" & dTue & "'}"
    sValues = sValues & ", {D '" & dWed & "'}"
    sValues = sValues & ", {D '" & dThu & "'}"
    sValues = sValues & ", {D '" & dFri & "'}"
    sValues = sValues & ", {D '" & dSat & "'}"

    sSQL = "INSERT INTO ""FiscalDay"" (" & sColumns & ") VALUES (" & sValues & ")"
    MsgBox(sSQL)

    oStatement.executeUpdate(sSQL)
    oFormFiscalDays.reload
End Sub

Here is an image of what the human readable sql statement looks like:

image Human Readable SQL

Thanks, Jon

2015-12-14 20:10:22 +0200 received badge  Famous Question (source)
2015-12-14 20:10:22 +0200 received badge  Popular Question (source)
2015-12-14 20:10:22 +0200 received badge  Notable Question (source)
2015-11-09 09:33:48 +0200 received badge  Famous Question (source)
2015-11-02 03:01:51 +0200 marked best answer LO Base: Multiple Primary Keys: Means 3 or more.

Hi all,

I'm trying to work on a database where some tables are best described by two or three primary keys. The trouble comes in trying to set up the relationships. A lot of times I get an error saying that a field needs to be unique. Well, it can't be unique, which is why I have three primary keys. Then sometimes when I do manage to set up a relationship, using the editor and manually selection the two tables and all the fields for each table and then selecting update cascade, it connects them, but no relationship numbers are present, do I don't know if it things 1 to 1 or 1 to many or 0 to 0, or what. Is there a really good easy to follow guide for setting up relationships with multiple primary keys? Or would someone be able to help with my database structure.

I tried to think of an easy to relate to example. The best I can come up with is a calendar, so I tried this as practice in Base instead of working with my complicated DB. The table Year, can be uniquely identified by a Year_ID, since the years don't repeat. However, the table month, can't be identified by just month, because the numbers 1 through 31 are repeated many times, but a compound key of ("Year","Month") would make that table unique. Then the table Day would only be unique with ("Year","Month","Day") as a primary key. When I link them, The Year PK in the Year Table is linked to the Year PK in the Month Table as a 1 to Many, which is right because a year will have many months, but that month with that id can only belong to one year. However, when I link the month and Day, I add the relationship manually, select the two tables, for the Month table I select Year and Month PKs, and for the Day table, I select Year and Month PKs. Then I select the Update Cascade radio button. When I click okay, I get two lines between the two tables, but there are no numbers.

Not the greatest example, but thought it simple enough to try.

EDIT: Here's the Base created SQL for the example I gave above:

SET DATABASE COLLATION "Latin1_General"

CREATE SCHEMA PUBLIC AUTHORIZATION DBA

CREATE CACHED TABLE ...
(more)
2015-10-15 12:29:26 +0200 asked a question LO Calc Macro change cell background color issue.

Hi all,

I have a really simple macro that I want to change the background color of a cell with. I will eventually have a column of cells with a hex value of a color, then in the next column the cells will have this macro to change the background color. I have it really simple right now to test it, but all it does is change the cell text to invisible and doesn't change the background color at all. I've searched the internet and found you are supposed to use CellBackColor. This is the test macro:

Function BGCOLOR(colorhex As String)
    ThisComponent.CurrentSelection.CellBackColor = clng("&H" & colorhex)
End Function

and this is how I am calling it:

=BGCOLOR("4d4d4d")

Is there a mistake in this that I just cannot see?

Thanks, Jon

2015-09-06 22:32:23 +0200 received badge  Famous Question (source)
2015-04-12 23:50:31 +0200 received badge  Famous Question (source)
2015-03-06 06:21:28 +0200 received badge  Popular Question (source)
2015-03-06 06:21:28 +0200 received badge  Famous Question (source)
2015-03-06 06:21:28 +0200 received badge  Notable Question (source)
2015-02-21 18:08:28 +0200 received badge  Notable Question (source)
2014-12-14 16:25:21 +0200 received badge  Popular Question (source)
2014-12-12 00:28:59 +0200 received badge  Famous Question (source)
2014-11-24 05:26:18 +0200 received badge  Notable Question (source)
2014-11-23 08:17:23 +0200 received badge  Notable Question (source)
2014-11-14 18:15:57 +0200 received badge  Notable Question (source)
2014-11-14 18:15:57 +0200 received badge  Famous Question (source)
2014-11-14 18:15:57 +0200 received badge  Popular Question (source)
2014-11-07 21:56:47 +0200 received badge  Popular Question (source)
2014-11-07 11:19:28 +0200 answered a question Base: How can I fix this case when to be parsed by LO and not HSQL Directly?

I did it, but it's really a bit of a hack, in my opinion. If anyone has a better way of doing it, please let me know as I know have a lot of sub-select statements going on, so the runtime of this on a large database must be huge. The solution is to replace TOP with MAX as follows, in case anyone needs the answer to this:

SELECT CASE 
      WHEN MOD("F3"."TOTAL", 2) = 1
         THEN "F3"."ID"
      ELSE NULL
      END AS "COL1"
   ,CASE 
      WHEN MOD("F3"."TOTAL", 2) = 1
         THEN "F3"."FruitName"
      ELSE NULL
      END AS "COL2"
   ,NULL AS "COL3"
   ,NULL AS "COL4"
FROM "FRUITS_ROWNUM_VIEW" AS "F3"
Where "TOTAL" = (SELECT MAX("TOTAL") FROM "FRUITS_ROWNUM_VIEW")
ORDER BY "F3"."TOTAL" DESC

Cheers.

2014-11-07 11:12:05 +0200 commented question Base: How can I fix this case when to be parsed by LO and not HSQL Directly?

After more studying, it appears that Base only supports TOP in SQL mode rather than processing it directly. Is there a way to return only one record in a Base friendly way?

2014-11-07 11:00:21 +0200 received badge  Enthusiast
2014-11-06 18:52:22 +0200 asked a question Base: How can I fix this case when to be parsed by LO and not HSQL Directly?

Hi All,

I have been trying get s table to display two records in the same result row using LO 4.1 and the embedded HSQLDB version. Sliderule provided a great example, but I want to be able to parse the sql with LO and not HSQL because I need to add variables to code. I thought I would try this as a learning experience. The last bit of code is this:

SELECT TOP 1 CASE 
      WHEN MOD("F3"."TOTAL", 2) = 1
         THEN "F3"."ID"
      ELSE NULL
      END AS "COL1"
   ,CASE 
      WHEN MOD("F3"."TOTAL", 2) = 1
         THEN "F3"."FruitName"
      ELSE NULL
      END AS "COL2"
   ,NULL AS "COL3"
   ,NULL AS "COL4"
FROM "FRUITS_ROWNUM_VIEW" AS "F3"
ORDER BY "F3"."TOTAL" DESC

It's part of a larger view, but I traced the issue to the above section. The other parts all work fine by themselves as I broke it up and tested smaller parts. This is the full code of the view:

// FRUITS_2COL_VIEW
SELECT 
    "A"."ID1"
   ,"A"."FruitName1"
   ,"A"."ID2"
   ,"A"."FruitName2"
FROM (
   SELECT 
       "F1"."ID" AS "ID1"
      ,"F1"."FruitName" AS "FruitName1"
      ,"F2"."ID" AS "ID2"
      ,"F2"."FruitName" AS "FruitName2"
   FROM "FRUITS_ROWNUM_VIEW" AS F2
   CROSS JOIN "FRUITS_ROWNUM_VIEW" AS F1
   WHERE ("F1"."TOTAL" + 1) = "F2"."TOTAL"
      AND MOD("F1"."TOTAL", 2) = 1

   UNION ALL

   (
      SELECT TOP 1 CASE 
            WHEN MOD("F3"."TOTAL", 2) = 1
               THEN "F3"."ID"
            ELSE NULL
            END
         ,CASE 
            WHEN MOD("F3"."TOTAL", 2) = 1
               THEN "F3"."FruitName"
            ELSE NULL
            END
         ,NULL AS "COL3"
         ,NULL AS "COL4"
      FROM "FRUITS_ROWNUM_VIEW" AS "F3"
      ORDER BY "F3"."TOTAL" DESC
      )
   ) AS "A"
WHERE "A"."ID1" IS NOT NULL
   AND "A"."FruitName1" IS NOT NULL

As you can see, I added in AS "COL1" and AS "COL2", trying to follow another format in a different post about this: http://ask.libreoffice.org/en/questio... They mention being able to do this in LO without needed HSQL to parse it directly.

The error that I get with the trouble section is the following:

SQL Status: HY000 Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

I hope someone can help me get this section working with LO's parser so I can add in variables.

Thanks, Jon

2014-10-30 11:05:42 +0200 asked a question LO Base: Return two records at a time in a sorted query.

Hi all,

I will use lots of table examples to describe this one.

I want to return a record and the next record at once in a sorted by ascending way. I have my "Fruits" table in a testing database with the following to describe this:

id  |  SomeText
0   |  Orange
1   |  Banana
2   |  Apple
3   |  Pear
4   |  Kiwi

However, it needs to be sorted in ascending order:

id  |  SomeText
2   |  Apple
1   |  Banana
4   |  Kiwi
0   |  Orange
3   |  Pear

Next, I want to display two records side by side, so I tried writing this: SELECT "p"."id", "p"."SomeText", "n"."id" AS "NextID", "n"."SomeText" AS "NextSomeText" FROM "Fruits" AS "n", "Fruits" AS "p" WHERE "n"."id" = "p"."id" + 1

Which displays this:

id  |  SomeText  |  NextID  |  NextSomeText
0   |  Orange    |  1       |  Banana
1   |  Banana    |  2       |  Apple
2   |  Apple     |  3       |  Pear
3   |  Pear      |  4       |  Kiwi

When I apply sorting to that, I get the next ID number instead of the next sorted fruit.

However, the final results that I want need to look something like this:

id  |  SomeText  |  NextID  |  NextSomeText
2   |  Apple     |  1       |  Banana
4   |  Kiwi      |  0       |  Orange
3   |  Pear      |  NULL    |  NULL

I hope this makes some type of sense.

Thanks.

2014-10-28 08:14:52 +0200 asked a question LO Base Report: Make an image hidden if there is not text for a line.

Hi,

I have a report that needs to be formatted in a specific way with a certain number of elements in a certain position. When there is a there are some fields where the text is optional, but have a drawing that goes along with that line of text if it's there. I would like to make that drawing invisible when there is no text in a certain field and without rearranging the position of all the other elements on the printed page. I have tried Conditional Print statements on the drawing, but the statements disappears when I save and exit edit mode and then reopen the report in edit mode. How can I do this?

Thanks,

2014-10-01 23:13:54 +0200 received badge  Popular Question (source)
2014-09-29 08:35:58 +0200 asked a question LO Base 4.1: Multiple records side by side on same page?

Hi all,

I need to have specially formatted prints of records of an exact size, the width of A6, but not as high. Currently, I just print it to A6 paper and cut off the extra bit on the line I put in. The main issue is that the printer prints at an offset of 2.5mm, so I have taken this into account by having a page header of that size, so when it prints, everything is perfectly lined up.

The challenge I have is that if we run out of A6 paper and need to print to A4 paper, it would make sense to print 4 to a page to minimize waste. I have tried the "Print 4 to a page" method in writer, however, since I have the 2.5mm header, that is also printed for the bottom row, thus making those ones 2.5mm taller.

Using the Report Builder in 4.1, is there a way to divide that repeating region in half, so I have have two of the templates side by side, then when it repeats down, there would be 4 to a page in the correct alignment and easy to cut out on the lines?

Thanks, Dycius

2014-09-29 08:23:37 +0200 received badge  Famous Question (source)
2014-09-16 12:02:09 +0200 received badge  Notable Question (source)
2014-09-16 12:02:09 +0200 received badge  Popular Question (source)
2014-06-28 23:13:00 +0200 received badge  Notable Question (source)
2014-05-10 21:38:25 +0200 received badge  Notable Question (source)
2014-05-05 15:11:42 +0200 received badge  Popular Question (source)
2014-04-30 22:17:41 +0200 asked a question LO Base: Link to a View served by external Database?

Hi all,

There are two different databases in two different offices controlled by two different teams. I work in one office with a team that inputs certain parts of the data into a LO Base database and we use the reports feature to make great looking prints from the data. The other team uses MySQL and has a table that has some shared fields to what we have but not everything. Instead of duplicating work, I'd like remove those fields from our database (since we copy what the other team does anyways) and make a query that joins a view from the MySql database with the data from ours so when I run report all the updated infor nation is compiled.

Is this possible? If so, how do I set this up?

Many thanks, Jon

2014-04-27 17:38:16 +0200 received badge  Notable Question (source)
2014-04-27 17:38:16 +0200 received badge  Famous Question (source)