[Solved] Keep data reference when inserting rows

Discuss the spreadsheet application
Post Reply
bennynoise
Posts: 11
Joined: Tue Jul 05, 2016 3:29 pm

[Solved] Keep data reference when inserting rows

Post by bennynoise »

Hello everyone,

I am struggling with the following problem: I am developing a spreadsheet to assess how many hours someone worked on a specific task. To achieve this, I have two sheets:

1) Tasklist: As the name suggests, this table contains only a list of tasks.
2) Hours: Gets the tasklist from the other table and has an additional column "hours" to enter the data.

Here comes the tricky part: Let's say the tasklist contains five tasks ("Task 1" to "Task 5") and we already entered the hours. Now we notice that we forgot "Task 1b" in the list. If I insert a new row in the tasklist, the tasks in the "hours"-table shift correspondingly but the data that I entered does not. Is there a way to fix this?

I attached a small example. The real application is somewhat more complicated, but the example should illustrate the problem.

Thank you for your help! :)

*edit: changed "table" to sheets, typo fixed
Attachments
tasks_example.ods
(9.7 KiB) Downloaded 82 times
Last edited by bennynoise on Fri May 17, 2019 9:26 am, edited 2 times in total.
Open Office 4.1.2
Mac OS X 10.11.5
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Keep data reference when inserting rows

Post by keme »

This is a typical case of integrity loss, where the ID field is "dynamic". The task name in column A Hours is fetched - by formula - from "same row number" in Tasklist. Column B contains explicitly entered data, with no link to other content. When you change the source table, the link between data items is lost.

In Calc, you need to enter that link in one way or other. There is no simple and reliable way to have the entered data automatically linked to a row in a different sheet in Calc ; you should enter task ID with task hours instead of fetching ID by formula. Data entry may be aided by data validation (using the Tasklist table as a validation source). For limited tasks (small data runs, small number of users) I'd suggest this.

With a database, this could be automated reliably. That requires a proper understanding of databases and of the job at hand. For larger tasks (large amounts of data, great complexity, many users, major economical impact, etc.) I'd make that effort, or hire someone with the adequate skills.

In Calc,the automation might be solved by macro programming, using forms, filters and pivot tables. This would be less portable, less extensible, and probably also less reliable, and requires a sound understanding of the task at hand, programming in general and the UNO/OpenOffice API. Don't go there if you don't know for certain that you must.
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Keep data reference when inserting rows

Post by MrProgrammer »

bennynoise wrote:I have to tables
No you don't. Calc is not a database. You have two sheets. Only careful spreadsheet design and careful usage can make it act as a database. It is difficult to create a design which inexperienced users cannot break accidentally.
bennynoise wrote:If I insert a new row in the tasklist, the tasks in the "hours"-table shift correspondingly but the data that I entered does not. Is there a way to fix this?
Click sheet Tasklist. Shift-click sheet Hours. Insert the row. Type "Task 1b" in column A and press Enter. Click sheet Hours. Enter the data in column B.
bennynoise wrote:The real application is somewhat more complicated, but the example should illustrate the problem.
Therefore the procedure suggested above for this example may not be suitable for your real application.
keme wrote:Data entry may be aided by data validation (using the Tasklist table as a validation source). For limited tasks (small data runs, small number of users) I'd suggest this.
This will be simple and reliable. When the task name is selected from data validation, it is a static value, not a formula, therefore you can add Task 1b at the bottom of the data in Hours and then use Data → Sort to reorganize it.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
bennynoise
Posts: 11
Joined: Tue Jul 05, 2016 3:29 pm

Re: Keep data reference when inserting rows

Post by bennynoise »

Thank you both for your detailed and helpful answers.
MrProgrammer wrote:
bennynoise wrote:I have to tables
No you don't. Calc is not a database. You have two sheets.
Sorry, I am using the German version of Calc in which sheets are called tables by default. Should've checked the translation more carefully.

I didn't know you could insert rows in multiple sheets at once. However, as you assumed, it doesn't work with the real application.

I think the procedure using data validation is indeed the best option. It may take a little longer to enter the tasks in the hours sheet (there will be one hours sheet for each employee, so tasks have to be entered or selected via dropdown multiple times), but losing data integrity by a missclick will be considerably more time-consuming and costly, for that matter.
Open Office 4.1.2
Mac OS X 10.11.5
Post Reply