Understanding data entry forms vs. exact forms & how to create a client recordID field by combining partial data from other fields.

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • Answered
This is probably a pretty basic concept but I'm very new to this. I'm trying to create a medical record ID# which is a combination of the client's first name 1st initial + first 3 letters of last name + last 4 digits of Medicaid ins ID, but have this field autmatically populate on all my medical record forms. So like if my name was John Smith Med ID= 23458373987849, then I'd like to enter that data into my clients parent table during the admissions intake appointment & have the record ID# field populate JSmi7849 automatically into the top right corner of each of my exact forms (admissions documents), & other client clinical documentation (i.e. progress notes, assessment report, Individualilzed service plan, discharge summary, etc.) Also, am I correct that the best way to produce these records is by using the "exact forms" & designing the custom templates in word or is there a better way to publish the information into an offical medical record document, which can be signed by the clniician. If I'm understanding Quickbase correctly, there are 2 types of forms: data entry forms (used to put the data into tables for storage) & exact forms (used to publish data into a formalized document after it's been entered. Is this correct?
Photo of Alex McClelland

Alex McClelland

  • 154 Points 100 badge 2x thumb

Posted 7 months ago

  • 0
  • 1
The medical ID formula is easy and I will post it if you need it.

To generate. Real document attachment on docs or pdf format, I recommend this product add on from a Juiced.

https://www.juicedtech.com/exact-form... All my clients use it as it generates an actual document and not just an exactly formatted html page.
Photo of Alex McClelland

Alex McClelland

  • 154 Points 100 badge 2x thumb
Yes please help with the medical ID formula!  I'm a clinical supervisor who is trying to develop the company's EHR system on the weekends so I have really no experience w/computer systems or programming.  This is all new to me & I have very limited time to try & teach myself how to do this stuff so I'd really appreciate the help.  As for the ap, I was already looking into that exact one & most likely will use it as long as it doesn't cost me too much or cause any type of problem w/my company's current Quickbase setup.  The systems guy created a "Clinical" ap and gave me administrator status to play around w/stuff this weekend so I'm already looking into that ap.
Photo of Alex McClelland

Alex McClelland

  • 154 Points 100 badge 2x thumb
Also, are you in any way affiliated with Juiced or did you just suggest that ap because you've had good reviews?  The reason that I'm asking is I have a concept of what I'm trying to accomplish & believe that ap would actually be a very solid investment for the company, but I'd have to be knowledgeable enough about its features and how they would benefit our process to advocate for consideration regarding it's purchase.  If you have any stake in that ap would you be able to answer some other basic questions to educate me further so I can adequately explain it's value to my systems guy?  If not, could you point me in the direction of where I could find someone who could help me with this?
I’m not affiliated with Juiced. I’m a Quick Base Solutions Provider who has implementned Juiced Exact Forms Plus dozens of times for my clients.

I can answer questions here or you can contact Juiced directly. Typically when my clients need a customer facing firm as opposed to just for internal use, then I help them implement EF+ or give them training tips on how to implement themselves.

The setup allowed for fields to be inserted in a Form and also embedded sub tables, typically child tables.
Photo of Alex McClelland

Alex McClelland

  • 154 Points 100 badge 2x thumb
I sincerely appreciate the guidance & we have a meeting scheduled for next Tuesday where I'll introduce the EF+, because it seems like it will really simplify what I'm trying to accomplish.  Right now I'm still trying to wrap my head around the table relationships setup that I need to effectively actualize the system.  They have already created a parent "clients" table with a new client input form for the admissions person to input new client data.  I'm trying to build out the system so that all the new client data input during the admissions/screening appointment is populated into the appropriate places throughout the respective admissions intake documents required in the medical record (i.e. client referral form, client FACE sheet, client release of information forms, patient registration form, screening form, etc.)  Currently the admissions person is having to handwrite the same information (client name, DOB, insurance info,etc.) into each form, which is extremely inefficient.  I saw in the youtube video on EF+ that they had created at document templates table & a document template subtables table.  I'm wondering if I should be creating an admissions intake forms table that is the child of the clients table but the parent of intake documents subtables maybe one for each separate intake document form? But my gut tells me that there's a much more efficient way to structure this.  Maybe just create the admissions intake documents table with all the fields that would be on each form (obviously none duplicated) & then just create an exact form for each required intake document w/the specific fields required that is linked to that table?  I'm still trying to understand how to form basic table relationships because it seems like most of the information fields for the various forms could be linked in from other tables such as the client table, insurance policy table, address table, etc., but then I'm not understanding where the user input for the rest of the data is performed.  Sorry if I'm not making much sense but any continued guidance would be greatly appreciated.
It’s difficult to help you lay out the best structure for your app on this forum. Obviously one on one would be better.

In general, you need to think of each table in terms of information that you only want to enter once.

So for example, yes you need a Patient table. Then likely, the same patient could get admitted more than once. So then you would have an admissions table where One parent has Many Admisisons. Then look up all the patient info down to the Admisisons table so that there no duplication of data entry.

I can continue to answer questions son this community forum, but if you decide that you are at a point where you would like one on one Assistance and coaching tips, then you can contact me via

The formula for the medical ID, but not tested, so post back if any problems, would be

var text Inital = left ([First Name],1);

var text LastThree = left([Last name],3);

var text Med = Right([medical ID]);

$Initial & $LastThree & $Med

Now, you asked if that could be the Record ID. The Te odd Id is a built in automatic field which is a number wheel which sequentially counts up as records are added.

If you want to make a field in that format to actually be the Key field to the table, then typically tha is done by having a field calculated, and then a form rule to copy that to the key field of the table. There is also an advanced technique which can be used in some circumstances to make they key field be a formula field.

Btw, I suggest that the Medical ID field be a text field as opposed to a numeric field as it’s a very large number. I will need to recheck my notes, but I think that the largest number that Quick Base can hold is 14 digits, which is the length of your field in your example. A Text field can be any length.
Photo of Alex McClelland

Alex McClelland

  • 154 Points 100 badge 2x thumb

I'm not sure if I entered your formula text incorrectly of if something needs to be changed about the code but I got the following error box:

Formula error -- Bad or missing arguments in function call

The types of the arguments or the number of arguments supplied do not meet the requirements of the function Right.

The function Right can be used with the followi9ng arguments:
- Right (Text t, Number n).
-Right (Text t, Text d).

You may need to do one of the following to fix the problem:

- Choose the right number and type of arguments.
- Use a type conversion function to convert the argumetns you are using to the correct type.
- Choose a different function.

Also, its just the last 4 digits of the Medicaid ID# in our internal Medical Record ID.  The full Medicaid ID number is actually 12-digits long but we use the JSmi & only the last 4 digits from the actual Medicaid ID #.  Thanks again!

Make this correction

var text Med = Right([medical ID],4);
Photo of Alex McClelland

Alex McClelland

  • 154 Points 100 badge 2x thumb
This worked Thanks!! & I Pm'ed you from your site about continued coaching
.. I checked my notes and the maximum digits is 13 for a numeric field.
Photo of Keith

Keith, Champion

  • 1,320 Points 1k badge 2x thumb
Hi Alex,
(and thank you Mark!)

This is Keith from Juiced Technologies.

Our demo app is here and from that app you can also create a quote for EF+ so you understand the pricing.

We have an automated setup wizard that will create the necessary tables for EF+ so no need to create those until you are ready to try EF+.

Any questions, please let me know.