How to prevent submitting form details that already exist as a record in the database

[mostwanted]

I have created a calendar that assigns lesson_times & class_rooms randomly. So far it has been working properly until a conflic arose, one lecturer being assigned 2 classes at the same time, another one one class_room being assigned different lectures at the same time! I wanna avoid these.

How can I avoid entering 2 critical & determinant details that are similar to other critical & determinant ones that already exist in the database?

Time on its own being similar to another recorded time value is ok, a single classroom_number value being similar to another one recorded is ok, the problem is having both time and classroom_number being similar or time & a lecturer being similar to ones recorded because that means a clash in lessons!

If the generated time & classroom number are similar to those that already exists I want the form to fail to submit with a flag message that warns the system user of a conflict thats causing failure to submit. (I have it in my head but i'm failing to put it on code)

Regards;

Mostwanted

[Dave S]

What you want is related to the standard validator IS_NOT_IN_DB() .  See

<URL:https://web2py.com/books/default/chapter/29/07/forms-and-validators#Database-validators>

That only checks one field at a time and I think it is really a tuple of fields that you are concerned about.  After all, there can be several classes at 10 on Tuesday, and it is only the combination of 10 on Tuesday with Room 310 that has to be unique, or the combination of 10 on Tuesday with  Professor Fusspot .

I would look at one of two approaches:

1) creating a calculated field from the fields of concern, and applying IS_NOT_IN_DB() to that.

or

2) Using the onvalidation() call backs to check further.

<URL:https://web2py.com/books/default/chapter/29/07/forms-and-validators#onvalidation>

You can also write a custom validator (the discussion is just down the page from IS_NOT_IN_DB()), but I'm not sure that is helpful here.

/dps

[mostwanted]

Hey Dave, thanks for replying,

This below is what I tried & it seems to be working, idont know if the best solution but please help e where u think I could improve it:

def my_validator(form):
    details=db(db.lecture).select()
    for d in details:
        if form.vars.lecturer==d.lecturer.id and form.vars.lecture_time==d.lecture_time:
            form.errors.lecturer=SPAN("There is a conflict with these values!", _style="font-weight: bold;")
            form.errors.lecture_time=SPAN("There is a conflict with these values!", _style="font-weight: bold;")

def index():
    details=db(db.lecture).select()

    form=SQLFORM(db.lecture)
    if form.process(onvalidation=my_validator).accepted:
        response.flash=T('Submitted')
    return locals()

[Dave S]

On Friday, July 17, 2020 at 2:38:43 AM UTC-7, mostwanted wrote:

Hey Dave, thanks for replying,

This below is what I tried & it seems to be working, idont know if the best solution but please help e where u think I could improve it:

def my_validator(form):
    details=db(db.lecture).select()
    for d in details:
        if form.vars.lecturer==d.lecturer.id and form.vars.lecture_time==d.lecture_time:
            form.errors.lecturer=SPAN("There is a conflict with these values!", _style="font-weight: bold;")
            form.errors.lecture_time=SPAN("There is a conflict with these values!", _style="font-weight: bold;")

def index():
    details=db(db.lecture).select()

    form=SQLFORM(db.lecture)
    if form.process(onvalidation=my_validator).accepted:
        response.flash=T('Submitted')
    return locals()

Looks good to me.  

/dps

[mostwanted]

Thank you

[villas]

Your my_validator function iterates through the whole table.  This is OK when you have a few records,  but very inefficient if you have thousands/millions.  Why not simply query the table?  Something like this...

def my_validator(form):


    if db((db.lecture.id == form.vars.id) & (db.lecture.lecture_time == form.vars.lecture_time)

         ).count() >0:

        form.errors.lecturer=SPAN("Record already exists", _style="font-weight: bold;")


[mostwanted]

Impressive outlook Villas, thank you.

[Dave S]

Ah, thank you.  I thought I was overlooking something, but late-night code reviews can be a challenge.

/dps

 

[mostwanted]

After a form has failed to save because that information already exists in the database I wanna try & alter the value that determines a day where the info is saved in the db for the form to be saved in a different day that does not contain similar form details currently attempting to be saved and this should be done automatically without the engaging the user E.G:

def my_validator(form):
    control=['1', '2', '3', '4' ,'5']
    form.vars.controller = random.choice(control)


    if db((db.lecture.id == form.vars.id) & (d.controller==form.vars.controller) & (db.lecture.lecture_time == form.vars.lecture_time)).count() >0:

    if int(form.vars.controller) < int(control[4]):
            int(form.vars.controller) +1
            response.flash=T('Saved in',' ', int(form.vars.controller) +1)
               
    if int(form.vars.controller) > int(control[4]):
            int(form.vars.controller) -1
            repsonse.flash=T('Saved in',' ', int(form.vars.controller) -1)

    else:
        form.errors.lecturer=SPAN("Record already exists in the whole database", _style="font-weight: bold;")

The above code doesnt work but its the concept thats in my head, where can I fix it?

On Tuesday, July 21, 2020 at 12:16:29 PM UTC+2, villas wrote:

[villas]

Suggestion:

Your idea of overriding the user's selections with random choices seems strange.

Why not ask the user to specify a date and then show him which classes are available nearest that date/time? 

He can then choose one of those.

[mostwanted]

Well the whole concept behind this application was that allocation of spaces in time, days & class rooms should be automatic, the user should just select the lecturer, the subject & the period the subject takes (Double session=2hrs, Single session=1hr) from there after the user submits that information its allocated time, day & class room automatically.

To determine the whole automatic process I used the random() method, the problem with it is that after a while of entering details some random determinants start to repeat & alot more than desired causing conflict & triggering (conflict handling methods) in my validation function!

With the above code I wanted to just keep up with the whole automatic notion of it!

[villas]

As you've discovered, random doesn't work in this context.  Make an array of class attendee numbers and then allocate the class which has the fewest students. 

[mostwanted]

Thank you Villas, you've been much helpful

[Asad Rashid]

The more simplest way is to set UNIQUE to the column of the database you want it to stay unique. That way a duplicate record will not be inserted and the insert query will return an error stating "record already exist". Check how to set your required column(s) "unique". This way is the fastest and easiest.