I am just beginning learning formspider.

Is it easy to do this on an editable grid:

col1 col2 col3 ---- ---- ---- val1 val2 val1+val2

col3 will display the values inputted on col1 and col2.

the at the footer or some panel, the sum of col3 will be displayed (sort of a grand total).

hope somebody can show me how. thanks.

asked 01 Oct '13, 06:03

jakz's gravatar image

jakz
133
accept rate: 0%


Hi Jakz,

To calculate a value for columns in a row, I created the following example.

I added two more columns to the EMPLOYEES query: BONUS (calculated as 0.5*SALARY) and TOTAL_INCOME (which is BONUS+SALARY originally)

Panel XML:

<panel>
  <tableLayout>
    <row>
      <cell>
        <grid dataSource="EMPLOYEES1">
          <column headerLabel="First Name">
            <textLabel column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textLabel column="LAST_NAME"/>
          </column>
          <column headerLabel="Salary">
            <textField column="SALARY">
              <events>
                <valueChanged action="calculateTotalIncome"></valueChanged>
              </events>
            </textField>
          </column>
          <column headerLabel="Bonus">
            <textField column="BONUS">
              <events>
                <valueChanged action="calculateTotalIncome"></valueChanged>
              </events>
            </textField>
          </column>
          <column headerLabel="Total Income">
            <textField editable="N" name="totalIncome" column="TOTAL_INCOME">
            </textField>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

PL/SQL code:

procedure calculateTotalIncome is
  v_row_t api_datasource.tt_row;
  v_total_nr number;
begin
  v_row_t:=api_datasource.getrow('EMPLOYEES1');
  v_total_nr:=v_row_t('SALARY').value_nr+v_row_t('BONUS').value_nr;
  api_datasource.setcolumnvalue('EMPLOYEES1.TOTAL_INCOME',v_total_nr);
end;

Hope this helps.

Kind Regards
Yalim

link

answered 01 Oct '13, 16:33

Yalim's gravatar image

Yalim ♦♦
2.8k5
accept rate: 21%

Hi Jakz,

Here is an example showing how you can calcualate the total salaries of employees shown in a grid:

Panel XML:

<panel>
  <events>
    <postInitialize action="calculateTotalSalary" ></postInitialize>
  </events>
  <tableLayout>
    <row>
      <cell columnSpan="2">
        <grid dataSource="EMPLOYEES1">
          <column headerLabel="First Name">
            <textLabel column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textLabel column="LAST_NAME"/>
          </column>
          <column headerLabel="Salary">
            <textField column="SALARY">
              <events>
                <valueChanged action="calculateTotalSalary"></valueChanged>
              </events>
            </textField>
          </column>
        </grid>
      </cell>
    </row>
    <row>
      <cell>
        <textLabel label="Total:">
        </textLabel>
      </cell>
      <cell>
        <textField name="total" editable="N" domain="DefaultNumber">
        </textField>
      </cell>
    </row>
  </tableLayout>
</panel>

PL/SQL Code:

procedure calculateTotalSalary is
  v_total_nr number;
begin
  execute immediate 'select sum(salary) from ('
                    ||api_datasource.getquery('EMPLOYEES1')||')' into v_total_nr;
  api_component.setvalue('mainPanel.total',v_total_nr); 
end;

Hope this helps.

Kind Regards
Yalim

link

answered 01 Oct '13, 16:04

Yalim%20Gerger's gravatar image

Yalim Gerger ♦♦
1.8k5
accept rate: 15%

edited 01 Oct '13, 16:20

Thank you very much Yalim.

By the way, where do I put the PL/SQL code?

link

answered 02 Oct '13, 03:13

jakz's gravatar image

jakz
133
accept rate: 0%

You have to connect to your oracle database and then use your favorite tool (like Oracle SQL Developer or maybe SQL-Plus) to create the procedure.. If you use SQL-Plus you would rather write create or replace procedure ... is...

link

answered 02 Oct '13, 04:06

Mazzel's gravatar image

Mazzel
31215
accept rate: 0%

Hello Yalim and Mazzel,

I tried Yalim's 2nd example but it is giving me an error:

Column "BONUS" does not exist Column "TOTAL_INCOME" does not exist

Where in FormSpider should I define these columns, since it is not a field of the table EMPLOYEES?

link

answered 03 Oct '13, 03:23

jakz's gravatar image

jakz
133
accept rate: 0%

Hi Jakz, You can create a database view on top of the employees table which has these columns.

(03 Oct '13, 03:29) Yalim Gerger ♦♦

Thanks Yalim. I get your idea now. By the way, if my grid has paging and I inputted values on different pages, will my values get saved in the table? I am still studying how or what api I need to create or call so that my changes get saved in an editable grid.

(03 Oct '13, 03:38) jakz
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:

×85
×2
×1

Asked: 01 Oct '13, 06:03

Seen: 2,181 times

Last updated: 03 Oct '13, 03:38


© Copyright Gerger 2017