Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

PDF to MySQL database

Avatar

Level 3

So here is the enigma. I set up a PDF form with fields that are correctly bound to the backend MySQL database - i.e. the connections work, I see and can chose the correct table values, all seems well. The submit (add), update buttons seem to work fine (i.e. the xfa code is correct) since all the data dissapears from the form, but when I query the database, no data ?

So where is the error? I get no error messages at all.

Advice always appreciated. Not sure if this is a MySQL end issue or not.

Shai

1 Accepted Solution

Avatar

Correct answer by
Level 6

You are trying to add a record that references a practitioner who's ID is 1, but there is no entry in the practitioner table with that ID.

Your emr table has a constraint that says that any practitioner that you reference (via practitioner_idPractitioner) must exist in the practitioner table.  If there are already rows in the practitioner table, choose one of the existing ids for your emr insert.  If not, add a row to the practitioner table first, then use that id for your emr insert.

Try to get all of your SQL statements working using the mySQL tools before you work on the form, it'll make things a lot easier if you  have less unknowns.

View solution in original post

7 Replies

Avatar

Former Community Member

No errors in the javascript console? hard to tell without seeing the form and having the DB.

Paul

Avatar

Level 3

So how familiar are you with MySQL databases? Having some issues and not getting anywhere from their forums. I did notice that I have to change the date format when sending data from PDF to MySQL db, since their syntax is yyyy-dd-mm

I did take some screen shots at the database - see what you think where I am going wrong. No data is accepted at all. No Javascript error from the PDF file either. Connections work fine and data seems to go, but not reside in the db. I even tried to enter the data manually and still could not get it working.

Thanks for the insight and help

sql2.JPGsql1.JPG

sql7.JPGsql4.JPGsql8.JPGsql5.JPG

Avatar

Former Community Member

So I am confused .....you are connecting to a mySql db to update a record or create a new one? Before you try that can you at least make the connection and get data from the table? Do you have the rights to update/create the record? It looks like the tool you are using to update the db generated an error as well (from the screen shots) so it does not look like it is a form issue.

Paul

Avatar

Correct answer by
Level 6

You are trying to add a record that references a practitioner who's ID is 1, but there is no entry in the practitioner table with that ID.

Your emr table has a constraint that says that any practitioner that you reference (via practitioner_idPractitioner) must exist in the practitioner table.  If there are already rows in the practitioner table, choose one of the existing ids for your emr insert.  If not, add a row to the practitioner table first, then use that id for your emr insert.

Try to get all of your SQL statements working using the mySQL tools before you work on the form, it'll make things a lot easier if you  have less unknowns.

Avatar

Level 3

Thanks Kevin

Guess I thought that since the ID's are required for creating the relationships between tables, I needed to make them a requirement, but I need to figure out how I can assign them automatically and increment them as new data is entered. Will work on the db some more - but any advice is greatly appreciated - I always seem to miss the small details.

Cheers

Shai

Avatar

Level 3

Thanks Paul - few mentioned that I have set a required field in my relational db, preventing any data from being accepted without data residing in the other tables. Working on fixing that, then back to the forms.

Cheers

Shai

Avatar

Level 6

I'm not sure how your form is set up, but generally when the user is entering data, the practitioner choice will be selected from a drop-down list that is populated from the practitioner table.  The id from the choice is then used in the insert into the ems table.  That way only existing ids will be used.  I don't recommend that you remove the constraint, since it can mess up the integrity of your database.