The following bug has been logged on the website:
Bug reference: 18527
Logged by: Vishnu Vardhan Vithanala
Email address: vishnu.vardhann05@gmail.com
PostgreSQL version: 16.3
Operating system: Windows
Description:
When I bulk uploaded some data using a csv file, it was all good. There were
543 rows in order and it went into the table and sat cleanly!
I've connected the same table to an input form. When I hit submit, the
latest entry which is supposed to be the last entry with a number id of 544
didn't go to the end of the table (bottom most) but instead sat on top of
all previous bulk uploaded entries.
Each entry has this kind of standard ID: DCMARIN10042024001
Subsequent entries will have the same ID with last 3 digits incrementing by
1.
DCMARIN10042024002
DCMARIN10042024003
...
...
DCMARIN10042024543
These ID's are generated using the below python code (flask):
def generate_unique_id(dept, sub_dept, table):
ProjectCode = ""
# Automatic Start Date capture - Production
#start_date = date.today()
# Start Date - Testing
start_date = datetime.strptime('01-08-2024', '%d-%m-%Y').date()
# To check if there is at least 1 entry in the DB or not
first_entry = table.query.first()
lastRow = str(table.query.order_by(table.id.desc()).first().id) if
first_entry else None
# Format Date in lastRow from String to Date Object
prev_date = datetime.strptime(lastRow[7:15], "%d%m%Y").date() if lastRow
is not None else None
# If today > latest date that is already existing in DB - For first
entry of today
if first_entry is None or start_date > prev_date:
ProjectCode =
f"{dept}{sub_dept}{start_date.strftime('%d%m%Y')}001"
# Subsequent entries for the same date
elif start_date == prev_date:
ProjectCode =
f"{dept}{sub_dept}{start_date.strftime('%d%m%Y')}{str(int(lastRow[15:18]) +
1).zfill(3)}"
return ProjectCode
The difference in ordering of entries between importing data from csv and
manually creating a new entry into the table is causing a hindrance in
generating proper project codes. When ordering the rows based on
project_code in either ascending or descending order, the latest entry which
I made using the form is not filtering properly. In short, the order is not
being applied to it! This is causing the generation of wrong & repeated
project code and dumping into a column that acts as primary key and which by
default is supposed to be unique! It's throwing an error that the key
already exists!
This issue based on my observation is due to the variation of data entry
into the table - between bulk upload and manual upload (using a form). When
I dumped the same data by using some python code based on pandas library and
running it through the csv file and using flask to create and save entries,
the issue seems to be resolved!
I'm not sure if this is a bug or not but it seemed like a problem that is
worth solving!