Create a database of input data

I am using Text Blaze as an input aid for my electronic medical record. I would like to use a snippet like this to create a database of input data and default to the previous data the next time I enter data.

Is it possible to do this using Data Blaze? If you could give me the basics of how to do this, that would be great.
Thanks again for the support of the Text Blaze community.

Date: {time: MMMM Do YYYY}
ID: {formtext: name=ID; cols=6}
Name: {formtext: name=Name; cols=20}
Main_Disease: {formmenu: Hypertension; Hyperlipidemia; Diabetes; name=Main_Disease; multiple=yes}
Blood_Pressure: {formtext: name=systolic bp; cols=3}/{formtext: name=diastolic bp; cols=3} mmHg
Comment: {formtext: name=Comment; cols=40}

Hi @N_Saito

Sure, you can. Please give this example a try:

{dbselect: SELECT Name, R_ID, Main_disease, `systolic bp`, `diastolic bp`, Comment FROM medical_records ORDER BY `created on` DESC LIMIT 1; space=id; menu=no}
Date: {time: MMMM Do YYYY}
ID: {formtext: name=R_ID; cols=6}
Name: {formtext: name=Name; cols=20}
Main_Disease: {formmenu: Hypertension; Hyperlipidemia; Diabetes; name=Main_Disease; multiple=yes}
Blood_Pressure: {formtext: name=systolic bp; cols=3}/{formtext: name=diastolic bp; cols=3} mmHg
Comment: {formtext: name=Comment; cols=40}
{dbinsert: INSERT INTO medical_records SET r_id=@r_id, name=@name, main_disease=@main_disease, `systolic bp`=`@systolic bp`, `diastolic bp`=`@diastolic bp`, comment=@comment; space=id; autoaddfields=yes}

I hope it helps.

@Abdalla_Mahmoud

Thanks, I'm getting closer to what I have in mind.

Is it possible to do it in such a way that when I enter an ID, it recalls the previous data of the patient with the same ID?

@N_Saito If I got you correctly, do you want to support two modes: adding a new patient, or updating an existing patient?

@Abdalla_Mahmoud Both, so when a new patient comes in, you put in a new ID and enter new data, and when an existing patient comes in, you put in an ID and the previous data is recalled.

@N_Saito I updated the example to fit your needs:

{if: r_id == ""}
{dbselect: SELECT R_ID, Name, Main_disease, `systolic bp`, `diastolic bp`, Comment, rowid() AS `Row ID` FROM medical_records ORDER BY `created on` DESC LIMIT 1; space=id; menu=no; isloading=not_initialized}{init_id=r_id}
{elseif: r_id <> init_id}
{dbselect: SELECT Name, R_ID, Main_disease, `systolic bp`, `diastolic bp`, Comment FROM medical_records WHERE R_ID = @r_id ORDER BY `created on` DESC LIMIT 1; space=id; menu=no; haserror=no_results}{init_id=""}{endif}
Date: {time: MMMM Do YYYY}
ID: {formtext: name=R_ID; cols=6}
Name: {formtext: name=Name; cols=20}
Main_Disease: {formmenu: Hypertension; Hyperlipidemia; Diabetes; name=Main_Disease; multiple=yes}
Blood_Pressure: {formtext: name=systolic bp; cols=3}/{formtext: name=diastolic bp; cols=3} mmHg
Comment: {formtext: name=Comment; cols=40}
{if: no_results}
{dbinsert: INSERT INTO medical_records SET r_id=@r_id, name=@name, main_disease=@main_disease, `systolic bp`=`@systolic bp`, `diastolic bp`=`@diastolic bp`, comment=@comment; space=id; autoaddfields=no}
{else}
{dbupdate: UPDATE medical_records SET r_id=@r_id, name=@name, main_disease=@main_disease, `systolic bp`=`@systolic bp`, `diastolic bp`=`@diastolic bp`, comment=@comment WHERE R_ID = @r_id; space=id; autoaddfields=no}
{endif}

I hope it helps.

@Abdalla_Mahmoud It seems to be working.
That's great.

I'll try some things, and if I have any more questions, I'll ask again, thanks.

@Abdalla_Mahmoud
I have tried many things.
I added the following code to read the patient IDs from the electronic medical record.

{r_id={site: text; page=https://karte.medley.life/*; select=ifneeded; selector=.names-num}}
{=r_id}

and it reads the IDs, but the Data Blaze data does not seem to update and I get the error below.

"Data Blaze Error
You must update exactly one row. Your query did not match any rows."

Thanks for all your help.

@N_Saito In that case, the update statement has run rather than the insert statement, and the fetched ID does not match any rows in the space, can you please share the full snippet to check where the issue is?

@Abdalla_Mahmoud

Thanks for your help.

{if: r_id == ""}
{dbselect: SELECT R_ID, Name, Main_disease, systolic bp, diastolic bp, Comment, rowid() AS Row ID FROM medical_records ORDER BY created on DESC LIMIT 1; space=5bvgja7ybFw3VMWXXmGUTO; menu=no; isloading=not_initialized}{init_id=r_id}
{elseif: r_id <> init_id}
{dbselect: SELECT Name, R_ID, Main_disease, systolic bp, diastolic bp, Comment FROM medical_records WHERE R_ID = @r_id ORDER BY created on DESC LIMIT 1; space=5bvgja7ybFw3VMWXXmGUTO; menu=no; haserror=no_results}{init_id=""}{endif}
Date: {time: MMMM Do YYYY}
ID: {formtext: name=R_ID; cols=6}
{r_id={site: text; page=https://karte.medley.life/*; select=ifneeded; selector=.names-num}}
{=r_id}
Name: {formtext: name=Name; cols=20}
Main_Disease: {formmenu: Hypertension; Hyperlipidemia; Diabetes; name=Main_Disease; multiple=yes}
Blood_Pressure: {formtext: name=systolic bp; cols=3}/{formtext: name=diastolic bp; cols=3} mmHg
Comment: {formtext: name=Comment; cols=40}
{if: no_results}
{dbinsert: INSERT INTO medical_records SET r_id=@r_id, name=@name, main_disease=@main_disease, systolic bp=@systolic bp, diastolic bp=@diastolic bp, comment=@comment; space=5bvgja7ybFw3VMWXXmGUTO; autoaddfields=no}
{else}
{dbupdate: UPDATE medical_records SET r_id=@r_id, name=@name, main_disease=@main_disease, systolic bp=@systolic bp, diastolic bp=@diastolic bp, comment=@comment WHERE R_ID = @r_id; space=5bvgja7ybFw3VMWXXmGUTO; autoaddfields=no}
{endif}

@N_Saito Let's have a call about it, I'll DM you.