Computed Columns in Spreadsheet

[Reto Eggenberger]

We are very interestet in the Play SQL Plugin for Confluence. And I have a Question or maybe feature request?

Is it possible to have a computed column in the spreadsheet.

Example:

We want a spreadsheet with articlenumber, articlename, quantity.

The user enters articlenumber and quantity. The articlename should be found automatically by the entered articlenumber. Maybe after a refresh of the page containing the spreadsheet?

Is this already possible?

Could such a feature added to the plugin?

Thx for any further information.

[Adrien Ragot 2 · Accepted solution]

Edit: Since Jan 11th, Play SQL 2.6 has column formulas. Thank you for asking!

Initial answer:

Hi Reto,

For now, we don't have calculated columns yet. The workaround is to write a query which displays both the spreadsheet and foreign fields from other tables. The "JOIN" wizard will help you do that.

About adding this feature, I can see two possible designs:

• We could have a "foreign key" renderer, so you enter an article number and the name displays. This would allow for searching articles by name or key.
• Or we could have computed columns. If the article number is right, the name is displayed in the computed column. The upside is, you could perform other types of calculation.

Is there one you like better?

Cheers,

Adrien

[Reto Eggenberger]

Hi Adrien

Thanks for the quick reply. So you can have a spreadsheet with 'data input fields' and 'reporting fields' from other tables/queries.

I think the 'workaround' with table join's is already pretty neat. With joins you are very flexible (if you can write sql). Multiple joins to get even more attributes sounds fantastic.

The Data (p.e. articlename) would come from our ERP-Server (MSSQL). So I would have to combine the spreadsheet (stored in Confluence, resp. postgre) with our MSSQL-Server. I will have to figure out the best way to do this. (I know MSSQL very well, but I'm new to postgre SQL.)

For a feature, I think computed columns sound more flexible then 'foreign key renderer'. But I'm perfectly happy with joins.

Cheers,

Reto

[Adrien Ragot 2]

Great if you're happy with joins, they already are in the software, in the Query editor. Do you need help for that?

Concerning MSSQL-Postgres integration, I know that PostgreSQL has a "foreign tables" feature, so it looks like a table in PostgreSQL, but each time you display it, Postgres connects to MSSQL and reads data. I have never used that one but here's the documentation about foreign tables.

You can comment on PLAYSQL-41 if you have anything to add. I'll add it to the next sprint, to be released after Christmas.

Cheers,

Adrien

[Reto Eggenberger]

Thank you for the tip with foreign tables. Sounds like 'view to linked server' in MSSQL. I will have to look into that.

Right now I'm a bit busy. So I will start with some testing in january.

Cheers, Reto

[Adrien Ragot 2]

Hi Reto,

I couldn't contact you by email but I've implemented the calculated columns. The power we can leverage from that feature is blowing because you can display either foreign keys, calculations, links computed from the cell's value, conditional text, etc. Thank you hundred times for requesting it.

See the release notes: http://documentation.play-sql.com/display/PUBLIC/Release+Notes+2.6

I'll update my initial answer to reflect this new status.

Cheers,

Adrien

[Reto Eggenberger]

Hi Adrien

Excellent timing. Tomorrow I will have a meeting with our Atlassian Expert and we have lots of use cases where PlaySQL would be a really good match.

Cheers,

Reto

[Adrien Ragot 2]

Hi Reto,

Excellent timing indeed, you'll even be able to test the feature before your meeting. Whatever the outcome I'd be interested in your approach, would you mind having a short phone call after meeting your expert? May I suggest Tuesday 6pm?

Regards, Adrien

[CelesteCS]

Hello!

For any calculations in Confluence, including calculations over tables, you may use CelesteCS Math for Confluence. There are versions for both Confluence Server and Confluence Cloud.

Assume you have a table with three rows and four columns: A till D with two rows and want to summarize elements from B2 to D2. Then here is the formula you should specify in A2:

SUM(Table1.B2:D2)

If you want to calculate average value, use AVG function:

AVG(Table1.B2:D2)

As you see, it is very simple and is similar to EXCEL with equal set of functions.

There is a plenty of other functions in the macro, so you may perform any calculations using formulas in EXCEL style.

Thanks!