Hi,

I am just playing around with conditional formatting of grid item and I've got it working after following one of the tutorials but now I want to try to get it working using the provided API's, specifically so I can change the foreground colour of "specific" items in a grid.

I've created a packaged procedure called "set_colour" which is added to the post-query event of the datasource I want to apply it to.

I tested the code using the following API call...

API_COMPONENT.setFontColor('CSR_OpenPN.PRI2',API_COLOR.RED);

This works as expected, i.e. it changes the foreground colour of all instance of the PRI2 item; so I then wanted to take it to the next level and colour just one instance of an item with...

API_COMPONENT.setFontColor('CSR_OpenPN.PRI2', API_COLOR.RED, API_COMPONENT.getValueTX('CSR_OpenPN.CSR2'), API_COMPONENT.getSelectedRowID('CSR_OpenPN.PRI2'));

and this second attempt does not work; am I specifiying the primary key and row ID incorrectly or am I doing something else wrong here?

Thanks,

Craig.

asked 05 Sep '14, 04:46

Craig's gravatar image

Craig
1.1k139
accept rate: 6%

edited 08 Sep '14, 03:43

formspider's gravatar image

formspider ♦♦
138210

Some more information...

I hardcoded my SetColour procedure to use a specific primary key value and record ID and it is sort of working; this is what happens when I run it in debug mode...

  1. Open panel, query runs and conditional formatting does not happen; however, in the debug window I see this message...

ERROR : 05-09-2014 14:50:47 OpenCSR_DS ( DBObjectAccess ) : PostQuery => SetColour : XXCAO_CCP_UTIL.set_colour ( ) => api_component.setFontColor => Specified component "PRI2" is not in a Grid.

"PRI" is the item I want to format.

  1. If I navigate to another panel and come back the first one then this time no new message shows in the debug window BUT the conditional formatting has worked.

Any ideas?

Thanks,

Craig.

(05 Sep '14, 09:51) Craig

Hi Craig,

I guess the Query On Initialize is enabled for the datasource of your grid. If so, please open the datasource edit dialog for your grid's dataource, uncheck the "Query On Initialize" checkBox and retry again.

Best Regards,
Ibrahim

link

answered 08 Sep '14, 07:17

Ibrahim%20Sandalli's gravatar image

Ibrahim Sand... ♦♦
1.5k5
accept rate: 25%

Hi Ibrahim,

I unchecked this option and this stopped my query from running on entry to the panel; so I coded a procedure to call API_DATASOURCE.nextPage and then invoked this in the postInitialise event for the panel; so my first question is, is this the correct way to programmatically invoke a query on a panel? (i.e. using this API).

The above seems to work in that is causes data to be returned, however, I'm still having trouble setting the colour on a record by record basis.

I've added some debug code to my SetColour procedure and one thing I immediately notice is that I'm not seeing debug messages for each record returned, it only seems to be running the post-query event once; is this is expected or should I see debug messages for all records?

Next, I am struggling set the setFontColour procedure, as per above it works when I want to set the colour for all records but not when I want to use it for setting the colour on just certain records; I've tried several combinations of passing the Primary Key value and a record ID to the setFontColor procedure but bothing works.

Can you provide a simple example of how to correctly use this procedure (setFontColor) where it is being used to set the colour of a single instance of an item?

This does somewhat highlight the need for better more comprehensive documentation for Formspider; whilst the tutorials are great and the API documentation is useful, when something is not known/documented then it can be tricky to solve without having to fall back on asking questions on this forum. I buy into the idea that some documentation can be avoid because of the way the IDE works but all other development tools/environments/languages provide lots of documentation and this is something I think is missing from FS.

Thanks,

Craig.

link

answered 09 Sep '14, 06:55

Craig's gravatar image

Craig
1.1k139
accept rate: 6%

Further to the above, it would also seem that why my Datasource Fetch Mode is "Paging" I get one Post-Query event (since I see output in the debug window) but when Fetch Mode is "Infinite Scroll" I get no Post-Query event because nothing is being displayed in the debug window.

Is this expected behavior or am I missing something?

Thanks,

Craig.

(09 Sep '14, 07:27) Craig

Hi Craig,

Regarding your question: "is this the correct way to programmatically invoke a query on a panel? "

Well yes and no. Do you want the user to remain in the same page of records when he comes back to the screen or do you want the page to be reset to the first record?

Calling the api_datasource.nextPage in postInitialize would just execute the next Page once. The code in postInit would not run if the user entered the screen(that is showing your Panel) again.

Also I'd call the api_datasource.executeQuery API instead of the nextPage API. I guess they effectively achieve the same thing but, sort of more obvious what the intent of the developer is with the executeQuery API.

As a side note; use postInit event very sparingly. You should not need it anymore. It was created to implement security (so that developers could show/hide components in a panel depending on the user privileges) but since 1.7 Formspider has a built-in security repository, so postInit is not needed for this purpose anymore. And everything else is usually better handled by other events.

Hope this helps.

Kind Regards,
Yalim

(09 Sep '14, 08:04) Yalim Gerger ♦♦

Thanks for you input Yalim; clearly I'm still experimenting with FS and have a lot of learn.

What events of which object would be a good candidate for using executeQuery?

Also, any thoughts on the main issue I'm having with Post Query and the setFontColor procedure?

Thanks,

Craig.

(09 Sep '14, 08:17) Craig

Hi Craig,

You are welcome. We'll write something on the core issue as well. Let me get the low hanging fruit first. :-)

There is an answer I wrote to another thread regarding pre-post Query event. postInit event on panels also falls into this category. Basically, I don't like them and avoid using them unless absolutely necessary (which is very rare). Here is the link to the thread which answers why I avoid using these events:

http://osqa.theformspider.com/questions/2868/pre-query-and-post-query-behaviour

Hope this helps.

Kind Regards,
Yalim

(09 Sep '14, 08:37) Yalim Gerger ♦♦

Hi Craig,

I created a small example that sets the font color of a specific item in a grid.

Create a datasource based on the COUNTRIES table that exists either in the SCOTT or HR schema (which one I forgot). Both schemas come with the Oracle database installation. The PK is set to COUNTRY_ID and the datasource's Query on Initialize check box is checked.

The panel is called mainPanel and its XML definition is:

<panel>
  <borderLayout>
    <cell docking="Center">
      <grid dataSource="COUNTRIES1">
  <column headerLabel="Country ID">
    <textField column="COUNTRY_ID"/>
  </column>
  <column headerLabel="Country Name">
    <textField name="countryName" column="COUNTRY_NAME"/>
  </column>
  <column headerLabel="Region ID">
    <textField column="REGION_ID"/>
  </column>
</grid>
    </cell>
    <cell docking="South" height="40">
      <button label="Button1">
        <events>
          <buttonPress action="action1"></buttonPress> 
        </events>
      </button>
    </cell> 
  </borderLayout>
</panel>

The panel points to an action called action1 which calls a PL/SQL procedure with one line of code:

procedure setFontColor is
begin
  api_component.setfontcolor('mainPanel.countryName',api_color.red,'DE');
end;

Note that "mainPanel.countryName" is a component in the mainPanel which resides in the Grid.

Clicking the button on the screen will make the font color of Germany red.

Hope this helps.

Kind Regards,
Yalim

link

answered 09 Sep '14, 09:04

Yalim%20Gerger's gravatar image

Yalim Gerger ♦♦
1.8k5
accept rate: 15%

Thanks for the example :-)

I can already get my code to work in a similar fashion (i.e. hardcoding) but what I'm struggling with is doing it based on data, i.e. the Primary Key passed to setFontColor is based on the data from a datasource that is currently displayed in a grid and specifically in the Post-Query event.

I too agree with you that the main application logic should not be coded in a post-query but in this particular case I need to use post-query to do conditional formatting (I know I can set background colour without having to do this but the actual font colour can't be changed using the same method).

The main stumbling block I'm at right now is it seems to only fire post-query once, I was expecting it to fire for each record (like in Oracle Forms).

Also, from within a post-query event, I'm getting the current row number using API_DATASOURCE.getRowNumber, is this the correct method of getting the row number that the post-query event has fired on?

Furthermore, if using the API_COMPONENT.getValueXX procedures from within the post-query event, should it return the values from the record that the post-query event fired on?

Thanks,

Craig.

link

answered 09 Sep '14, 12:19

Craig's gravatar image

Craig
1.1k139
accept rate: 6%

Hi Craig,

In your case, I'd do the following:

I'd create a procedure called showMyScreen and both execute the query and change the font colors in this procedure (more on this below). I would not use the post-query event. It doesn't work the way you expect it to anyway. (fires once per query execution not once for each row retrieved.)

Below is the code that changes the font color of all the country names to red. I only updated the setFontColor procedure from the example above. The new setFontColor procedure is below:

procedure setFontColor is
  v_rows_t api_datasource.tt_rows;
begin
  api_datasource.getrows('COUNTRIES1',v_rows_t);
  for i in 1..v_rows_t.count loop
    api_component.setfontcolor('mainPanel.countryName',api_color.red,v_rows_t(i)('COUNTRY_ID').value_tx);
  end loop;  
end;

You can read more about the getRows API in the API Documentation.

Hope this helps.

Kind Regards,
Yalim

link

answered 10 Sep '14, 04:09

Yalim%20Gerger's gravatar image

Yalim Gerger ♦♦
1.8k5
accept rate: 15%

Hi Yalim,

I think this will work and give the desired effect, I will try it out later; however, a concern would be for large datasets; correct me if I'm wrong but the above would need to load the entire dataset into the local PL/SQL table which would of course mean all rows would have to be fetched from the database, is this correct?

If so then this could present an issue when working with a lot of data, not so much in terms of memory usage (although that could be a problem) but in terms of performance.

Thanks,

Craig.

(10 Sep '14, 09:40) Craig

Hi Craig,

getRows does not run the query of the datasource. It fetches the rows that exist in the datasource.

Memory usage is also optimized. The getRows uses NO COPY clause for the v_rows_t in our parameter.

getRows runs very fast. We never had any performance issue at all.

Kind Regards, Yalim

(10 Sep '14, 09:58) Yalim Gerger ♦♦
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×2
×1
×1

Asked: 05 Sep '14, 04:46

Seen: 1,208 times

Last updated: 10 Sep '14, 09:58


© Copyright Gerger 2017