How to programatically update the FieldAliases?

joost

I am creating stacked bar graphs from cross-tab queries by dynamically adding a series for each of the columns. The problem is that -due to the nature of a cross-tab query- the columns in my queries differ based on user input while the frxADOQuery.FieldAliases through which I loop to collect field names, are set to those that I set at design time.

My questions:

1) How can I programatically update the FieldAliases from the report code (PascalScript), equivalent to the [Update] button in the Edit Aliases dialog?
2) Alternatively, can I ignore the FieldAliases and access the fields of the frxADOQuery in my PascalScript to get the field names directly?
3) Anyway, is this the best way to produce a stacked bar graph from a cross tab query or is there an easier way that I overlook?

thanks!

technisoft

joost wrote: »

2) Alternatively, can I ignore the FieldAliases and access the fields of the frxADOQuery in my PascalScript to get the field names directly?

I would try to do that mapping in the query itself.

One query might read:
SELECT
FIELD1 AS COLUMN1,
FIELD2 AS COLUMN2
FROM TABLE

another query could be:
SELECT
FIELD5 AS COLUMN1,
FIELD6 AS COLUMN2
FROM TABLE

etc.

joost

Hi, thanks but the problem is that the query is a cross-tab/pivot query. That means that, for example, based on one user input I might have fields X, A, B, C, D but based on another input I might only have fields X, A, C, D. Since the FieldAliases (property of TfrxADOQuery) are stuck on the first list (including fieldB) I will get an error when looping through the FieldAliases that include the non-existent field B. Hence my questions to 1) programatically update the FieldAliases, or 2) loop through the actual query fields.

technisoft wrote: »

technisoft wrote: »

2) Alternatively, can I ignore the FieldAliases and access the fields of the frxADOQuery in my PascalScript to get the field names directly?

I would try to do that mapping in the query itself.

One query might read:
SELECT
FIELD1 AS COLUMN1,
FIELD2 AS COLUMN2
FROM TABLE

etc.

technisoft

joost wrote: »

Hi, thanks but the problem is that the query is a cross-tab/pivot query. That means that, for example, based on one user input I might have fields X, A, B, C, D but based on another input I might only have fields X, A, C, D. Since the FieldAliases (property of TfrxADOQuery) are stuck on the first list (including fieldB) I will get an error when looping through the FieldAliases that include the non-existent field B. Hence my questions to 1) programatically update the FieldAliases, or 2) loop through the actual query fields.

I have never used FieldAliases so I may be well off with my suggestion.
My suggested method relies on the query being built dynamically, which means it probably has to happen in the application outside of the report/script. The different number of selected columns could then be handled by casting.

SELECT X AS C1, A AS C2, B AS C3, C AS C4, D AS C5 FROM TABLE

SELECT X AS C1, A AS C2, CAST(0 AS INTEGER) AS C3, C AS C4, D AS C5 FROM TABLE

You should also be able to cast NULL to any data type and strings to VARCHAR(xx)

joost

Thanks for the suggestions but I finally managed to solve it myself along the lines of refreshing the FieldAliases (list of the TfrxADOQuery).
All that is needed is to explicitly clear the FieldAliases (TfrxDataset.FieldAliases.Clear) which apparently triggers it to refresh based on the current dataset rather than the one used at design time.

technisoft

joost wrote: »

Thanks for the suggestions but I finally managed to solve it myself along the lines of refreshing the FieldAliases (list of the TfrxADOQuery).
All that is needed is to explicitly clear the FieldAliases (TfrxDataset.FieldAliases.Clear) which apparently triggers it to refresh based on the current dataset rather than the one used at design time.

Does that not just create a 1 to 1 mapping of the datafields? IOW, it is like using the original name of the field (without alias) and thus the whole aliasing thing is superfluous?

What am I missing here?

joost

technisoft wrote: »

technisoft wrote: »

Thanks for the suggestions but I finally managed to solve it myself along the lines of refreshing the FieldAliases (list of the TfrxADOQuery).
All that is needed is to explicitly clear the FieldAliases (TfrxDataset.FieldAliases.Clear) which apparently triggers it to refresh based on the current dataset rather than the one used at design time.

Does that not just create a 1 to 1 mapping of the datafields? IOW, it is like using the original name of the field (without alias) and thus the whole aliasing thing is superfluous?

What am I missing here?

Indeed it does 1:1 mapping of the datafields but that was just what I needed because I could not find how to access the data field fieldnames directly (hence my original Question 2). When I tried to get the fieldnames (using MyDataSet.GetFieldList() ) I would get whatever was (automatically) stored in FieldAliases and that was not up to date with the actual dataset (my Question 1).