[libreoffice-users] Schema in Calc
Hi, In a relationall DB there is a facility to display all the tables and this also shows how the tables are related and also which fields are related and how they are related. This is extremely useful to get an overall pcture of how things fit together and, importantly in this case, what the effects would be of changing something in one table on the data in the other tables. Does such a feature exist in Calc? With a large number of sheets and many cells looking up data in cells on other sheets it would be great to be able to see how changing a sheet name or a column heading would affect the rest of the data. Thanks -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Schema in Calc
Hi :) I seriously doubt it! It is database functionality. It would be like expecting a word-processor to have DTP functionality, or a text-editor to do the same things as a word-processor. One option might be to get Base to read the tables as it's external back-ends. That would get you to the same screen showing all the different tables/work-sheets and their headings. However since the calculations take place within the work-sheets Base wouldn't show any links between the tables just yet. Although you don't see the links directly it might help you figure out the obvious ones and a print-out (or screen-shot) of that screen might help you be able to draw in the rest as you figure them out by hunting through the worksheets. This process might help you figure out how to set-up Queries to do the work that is mostly done within worksheets at the moment and that would probably increase reliability quite a lot. So, that route might help you migrate the whole work-book and all it's sheets into a proper database. One problem with spreadsheets is that a column or row might not have every field doing the same calculation as all the rest in the row or column. People sometimes put a lot of effort into finding errant cells that misbehave in that way and there are a lot of tools to help track such cells down. A database allows you to write a (or modify) a calculation (formula) in one place, in a Query usually. Then you can be certain that exactly that formula is applied to every single line with no exceptions (although IF type statements help deal with special cases). The Query contains no actual data and only has the formula written once so it's extremely light-weight but when viewed as a table it looks like one of the tabs (a worksheet) within a spreadsheet. If you want it to look pretty then set-up a Form or Report to present the output of the Query in a more pleasant manner. Errr, part of the power of spreadsheets is that it does have the flexibility to have very different calculations in a column or row but then each cell needs to be labeled usually in an adjacent cell/field so that you know what it's for a few months or years later. Unfortunately many people keep using spreadsheets to do what a database would do better = which is ok as part of the planning process but often becomes unwieldy in the longer-term. Regards from Tom :) On 30 July 2014 07:37, Pat Brown mistyha...@gmail.com wrote: Hi, In a relationall DB there is a facility to display all the tables and this also shows how the tables are related and also which fields are related and how they are related. This is extremely useful to get an overall pcture of how things fit together and, importantly in this case, what the effects would be of changing something in one table on the data in the other tables. Does such a feature exist in Calc? With a large number of sheets and many cells looking up data in cells on other sheets it would be great to be able to see how changing a sheet name or a column heading would affect the rest of the data. Thanks -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Schema in Calc
The most of which I am aware is the ability to see which cells reference the current cell, and, even then, you won't see the cells if they are not visible on screen. It requires a macro to turn on and off. It is rather obscure; I think it is meant for debugging. On 07/30/2014 02:37 AM, Pat Brown wrote: Hi, In a relationall DB there is a facility to display all the tables and this also shows how the tables are related and also which fields are related and how they are related. This is extremely useful to get an overall pcture of how things fit together and, importantly in this case, what the effects would be of changing something in one table on the data in the other tables. Does such a feature exist in Calc? With a large number of sheets and many cells looking up data in cells on other sheets it would be great to be able to see how changing a sheet name or a column heading would affect the rest of the data. Thanks -- Andrew Pitonyak My Macro Document: http://www.pitonyak.org/AndrewMacro.odt Info: http://www.pitonyak.org/oo.php -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Schema in Calc
While what Tom said about databases versus spreadsheets is largely true, it is also true that a lot of people use spreadsheets as a sort of report, without the database bit. This is often easier to put together, especially for people that don't have the know-how to develop a database application complete with reports, and sometimes easier to maintain, especially in situtations where the requirements change often, even if only slightly. And what the OP asked for should be fairly trivial to implement, at least for a simple case. I don't think it'll ever be possible to see a proper schema, not given how flexible spreadsheets are, but seeing some sort of cell dependencies should be possible. And in fact, looking through Calc, I have found exactly that. Under Tools | Detective you have both Trace Precedents and Trace Dependents which show you what cells a given cell depends on, and what cell depend on a given cell. This should be most of what the OP needs. You can use Fill Mode to select multiple cells, but it doesn't seem to work on a whole page at a time, nor very well across pages. What would be better is a filterable list of formulae, and given that all non-empty cells have to be saved in the .ods file, it must surely be relatively trivial to pull out a list of formulae (and any other dependencies that it might be possible to make), present that list in a filterable manner, and allow the user to click to see the depender and dependee of the formula in some fashion, by highlighting the cells or double-clicking to go to them. At least that way you could, for example, pull up a list of formulae in the current spreadsheet, filter that list by one of the worksheets, and see all the formulae that depend on the worksheet name, or on a specific column in the worksheet, etc. This may not allow one to get a grand overview of how the data hangs together like with a database schema, but that is the price you pay for having the flexibility to have the data not hang together in a specific way. It would allow one to check how certain pieces hang together, and to establish before making changes what the effects might be. Just my thoughts Paul On Wed, 30 Jul 2014 09:53:54 +0100 Tom Davies tomc...@gmail.com wrote: Hi :) I seriously doubt it! It is database functionality. It would be like expecting a word-processor to have DTP functionality, or a text-editor to do the same things as a word-processor. One option might be to get Base to read the tables as it's external back-ends. That would get you to the same screen showing all the different tables/work-sheets and their headings. However since the calculations take place within the work-sheets Base wouldn't show any links between the tables just yet. Although you don't see the links directly it might help you figure out the obvious ones and a print-out (or screen-shot) of that screen might help you be able to draw in the rest as you figure them out by hunting through the worksheets. This process might help you figure out how to set-up Queries to do the work that is mostly done within worksheets at the moment and that would probably increase reliability quite a lot. So, that route might help you migrate the whole work-book and all it's sheets into a proper database. One problem with spreadsheets is that a column or row might not have every field doing the same calculation as all the rest in the row or column. People sometimes put a lot of effort into finding errant cells that misbehave in that way and there are a lot of tools to help track such cells down. A database allows you to write a (or modify) a calculation (formula) in one place, in a Query usually. Then you can be certain that exactly that formula is applied to every single line with no exceptions (although IF type statements help deal with special cases). The Query contains no actual data and only has the formula written once so it's extremely light-weight but when viewed as a table it looks like one of the tabs (a worksheet) within a spreadsheet. If you want it to look pretty then set-up a Form or Report to present the output of the Query in a more pleasant manner. Errr, part of the power of spreadsheets is that it does have the flexibility to have very different calculations in a column or row but then each cell needs to be labeled usually in an adjacent cell/field so that you know what it's for a few months or years later. Unfortunately many people keep using spreadsheets to do what a database would do better = which is ok as part of the planning process but often becomes unwieldy in the longer-term. Regards from Tom :) On 30 July 2014 07:37, Pat Brown mistyha...@gmail.com wrote: Hi, In a relationall DB there is a facility to display all the tables and this also shows how the tables are related and also which fields are related and how they are related. This is extremely useful to get an overall pcture of how
