Computed Columns in Spreadsheet

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.

2 answers

1 accepted

This widget could not be displayed.

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

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

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

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

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

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

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

This widget could not be displayed.

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!

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Aug 22, 2018 in Marketplace Apps

How a Marketplace app tech team is achieving gender diversity

Hello! My name is Genevieve Blanch, and I'm the Marketing Manager at RefinedWiki, creators of apps to give teams the tools to customize Atlassian platforms. Currently, 44% of the tech team at Re...

513 views 3 18
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you