ParamQuery grid support forum

General Category => Help for ParamQuery Pro => Topic started by: chirag on July 02, 2018, 05:35:02 pm

Title: javascript formula on cell
Post by: chirag on July 02, 2018, 05:35:02 pm

how to set formula on particular cell?

Title: Re: javascript formula on cell
Post by: paramvir on July 03, 2018, 08:06:33 am

js formulas apply to whole column.

https://paramquery.com/pro/demos/grid_formula

In order to make it cell specific, use condition based on rowData, and return existing cell data for other rows.

Code: [Select]

[6, function (rd, column) {
if(rd[1] == "BP"){ //condition based on rowData
return rd[3] + rd[4];
}
else{
return rd[column.dataIndx]; //return existing cell data
}
}],


Title: Re: javascript formula on cell
Post by: chirag on July 03, 2018, 09:37:41 am

I  don't want to apply on whole column.
I want to apply it on specific cell.
Is there any way to do that?

Title: Re: javascript formula on cell
Post by: paramvir on July 03, 2018, 10:52:15 am

As mentioned, above solution applies js formula to a single cell.

Are there more details of your requirement you want to share?

Title: Re: javascript formula on cell
Post by: chirag on July 03, 2018, 11:09:31 am

In my grid only 1st 3 rows are editable , and i want to show summary kind of thing in 1st row only for that 3 rows.

Title: Re: javascript formula on cell
Post by: paramvir on July 03, 2018, 06:38:23 pm

Please check this:

https://plnkr.co/edit/EbB4jYadOhYvpwNDoaxO?p=preview

There is Excel formula defined for Revenues column in first row.

Code: [Select]

            { rank: 1, company: 'Exxon Mobil',
              pq_fn: {                     
                  revenues: 'SUM(C2:C3)'
              }   
            },


Title: Re: javascript formula on cell
Post by: chirag on July 04, 2018, 09:30:16 am

My data is load from server side, so i can't declare it on datamodel.
Is there any way to declare formula after grid bind?

Title: Re: javascript formula on cell
Post by: paramvir on July 04, 2018, 12:07:37 pm

Yes it can be declared after load data from remote in dataModel.getData callback

https://paramquery.com/pro/api#option-dataModel-getData

In this callback get reference to first row and apply pq_fn as below, substitute the appropriate variable names.

Code: [Select]

  remotedata[0].pq_fn.revenues = 'SUM(C2:C3)'


Title: Re: javascript formula on cell
Post by: chirag on July 04, 2018, 02:29:00 pm

can you provide fiddle for this?

Title: Re: javascript formula on cell
Post by: paramvir on July 05, 2018, 09:57:07 am

Have you tried it. What problem are you facing.

Title: Re: javascript formula on cell
Post by: chirag on July 05, 2018, 04:11:16 pm

Hi,

I achieve this by adding one row at top of datamodel array.


getData: function (response) {
                    response.data.splice(0, 0, {
resourceType: 'Summary External', pq_fn: {
                            jan: 'IF(ROUND(SUM(E2:E4),1)=0,"",ROUND(SUM(E2:E4),1))', feb: 'IF(ROUND(SUM(F2:F4),1)=0,"",ROUND(SUM(F2:F4),1))',
                            mar: 'IF(ROUND(SUM(G2:G4),1)=0,"",ROUND(SUM(G2:G4),1))', apr: 'IF(ROUND(SUM(H2:H4),1)=0,"",ROUND(SUM(H2:H4),1))',
                            may: 'IF(ROUND(SUM(I2:I4),1)=0,"",ROUND(SUM(I2:I4),1))', jun: 'IF(ROUND(SUM(J2:J4),1)=0,"",ROUND(SUM(J2:J4),1))',
                            jul: 'IF(ROUND(SUM(K2:K4),1)=0,"",ROUND(SUM(K2:K4),1))', aug: 'IF(ROUND(SUM(L2:L4),1)=0,"",ROUND(SUM(L2:L4),1))',
                            sep: 'IF(ROUND(SUM(M2:M4),1)=0,"",ROUND(SUM(M2:M4),1))', oct: 'IF(ROUND(SUM(N2:N4),1)=0,"",ROUND(SUM(N2:N4),1))',
                            nov: 'IF(ROUND(SUM(O2:O4),1)=0,"",ROUND(SUM(O2:O4),1))', dec: 'IF(ROUND(SUM(P2:p4),1)=0,"",ROUND(SUM(P2:p4),1))',
                            total: 'IF(ROUND(SUM(E1:O1)/12,1)=0,"",ROUND(SUM(E1:O1)/12,1))'
}


Thanks for help.