How to Modify a saved query by code

[gkick]

Hi,

This is sort of a duplicate post because a comment does not accept my image.
How do I add fields programmatically to a query for a generic scheduling system?

The rows of this subform will see dates for 10 year aka 3652 days and will be filtered, the columns will be filtered as well via visible prop as shown in the second pic.
So how can I add D1,D2 etc

@frofa Thank you for the link, but it does not answer my question wether this is possible.

[frofa]

It probably is possible (to develop a macro to add the new columns), but will it provide you with a workable and flexible solution in the long run? There are many ways to cut a cookie. I am not skilled in creating basic macro code, so you’ll have to rely on someone else for that.

[gkick]

@frofa No worries, ta

[gkick]

Thanks for that @Ratslinger , all Ok with you? Have not seen you online for some time…

[Ratslinger]

@gkick,

All OK. Needed a mental break. Too much happening. Just need to slow down.

Much appreciate your concern. Best to you also.

[frofa]

See my comment.

[Ratslinger]

Hello,

You can access, update, insert or delete queries from a macro:

Rem Get access to Queries
    oDatabaseFile = ThisComponent.Parent.CurrentController.DataSource
    oQuery = oDatabaseFile.getQueryDefinitions()
Rem Verify "YOUR_QUERY" query exists - if not, create one
    If NOT oQuery.hasbyname("YOUR_QUERY") Then
        oQueryDefinition = createUnoService("com.sun.star.sdb.QueryDefinition")
        oQueryDefinition.Command = sSQL
        oQuery.insertByName("YOUR_QUERY", oQueryDefinition)
    EndIf
Rem Get access to the query needed for basis all information
    stQuery = oQuery.getByName("YOUR_QUERY")
    sSQL = stQuery.Command

The problem you then have is editing the string. This would require you manipulating it to your requirements using various string functions. You would need to break it apart & rebuild with various string functions.