[Solved] Reset Column IDs after one ID is deleted

Discuss the database features
Post Reply
MINTHETNAUNG
Posts: 18
Joined: Mon Jun 18, 2018 3:33 am

[Solved] Reset Column IDs after one ID is deleted

Post by MINTHETNAUNG »

Hi, I would like to ask that is there a way to reset the Column ID row if one whole row is deleted i.e. one ID is deleted? If one ID is deleted, there is a gap between the ID numbers as they are basically incrementing by 1. (ID 1,2,3,4,5,...). If then, I can't go from one record to another since I use ID to track the row number.
Last edited by MINTHETNAUNG on Wed Jun 20, 2018 9:58 am, edited 2 times in total.
Openoffice 4.1.5 on Windows 10
FJCC
Moderator
Posts: 9231
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Reset Column IDs after one ID is deleted

Post by FJCC »

I think you mean to ask if the row ID can be reset. I don't think there is a way to do that and it shouldn't be necessary. I see that you say that you are using the row ID to go from one record to the next. Can you explain more about why you need to do this? You can order the records based on the value of some field, e.g. the ID field, and you can do that with a query.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
MINTHETNAUNG
Posts: 18
Joined: Mon Jun 18, 2018 3:33 am

Re: Reset Column IDs after one ID is deleted

Post by MINTHETNAUNG »

Yes, you are correct. I am using row ID to go from one record to the next. My Client wants to see the record row by row, which I am using Calc Macro to access the Base table where I store the data. Thus, from Calc, I want my program to show the records from Base table (database) row by row. According to you, how can I do with a query?
Openoffice 4.1.5 on Windows 10
FJCC
Moderator
Posts: 9231
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Reset Column IDs after one ID is deleted

Post by FJCC »

Assuming your table is named MyTable

Code: Select all

SELECT * 
FROM "MyTable" 
WHERE [meet whatever conditions are needed] 
ORDER BY "MyTable"."ID"
That will return all of the columns for every row for which the WHERE conditions are met and the rows will be ordered by the ID column in ascending order.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
MINTHETNAUNG
Posts: 18
Joined: Mon Jun 18, 2018 3:33 am

Re: Reset Column IDs after one ID is deleted

Post by MINTHETNAUNG »

I see. I got all the records. But I want to show one record by one record only (not showing all at once). How do I do that? In my case, I can't show a record one after another if one of the records is deleted (i.e. ID is deleted) as I am using ID to trace the row number.
Openoffice 4.1.5 on Windows 10
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Reset Column IDs after one ID is deleted

Post by UnklDonald418 »

With the main Base window open select Forms and you can Use Wizard to Create Form
On the first screen you can choose either a query or a table as the data source and select which fields you want to appear on the form.
When you get to the Arrange Controls page there are 4 options. Choose any except the As Data Sheet option.
At that point if you select Finish a form will be created that will display the first record in the database. There are some navigation controls at the bottom of the screen that will allow you to step through the database one record at a time or type in a Record number to jump to a specific record .
The Record number is independent of the ID field
No spreadsheet required.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Reset Column IDs after one ID is deleted

Post by Villeroy »

The attached form document is a stand-alone form (not embedded in a Base document) and linked to the "biblio" table of the "Bibliography" database that is shipped with our office suites. You can navigate, sort and filter by means of the floating navigation toolbar or the navigation control attached to the form.
Attachments
biblio_form.odt
(10.89 KiB) Downloaded 287 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Reset Column IDs after one ID is deleted

Post by Villeroy »

P.S.: Resetting the auto-IDs is a very bad idea. The IDs are not row numbers. They identify a unique record. They are of no interest to the user of a database. They serve an entirely technical purpose. They have no meaning in the real world.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
MINTHETNAUNG
Posts: 18
Joined: Mon Jun 18, 2018 3:33 am

Re: Reset Column IDs after one ID is deleted

Post by MINTHETNAUNG »

Thank you - UnklDonald418 & Villeroy. That is a great help. I know resetting the auto-IDs is a very bad idea. But how could I trace the row record in Calc macro i.e. row records are from Base Database table?
Openoffice 4.1.5 on Windows 10
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Reset Column IDs after one ID is deleted

Post by UnklDonald418 »

Calc is a spreadsheet program designed to display and manipulate data on a grid.
There are numerous examples on this forum of people unsuccessfully trying to force a spreadsheet to work like a database.
The point of my message was, if you need to process the records one at a time, Calc is the wrong program. Use the database tools.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply