Issue and Expiration Dates for Many Fields

[kpcollier]

I am creating a table that keeps track of safety/training certificates for our employees. There are up to 20-30 different trainings our employees get. Most of these have issue and expiration dates. I was thinking that creating a table with Yes/No fields for each training, so that there is only one record per employee that just keeps getting updated instead of many records of the same user for different trainings. However, like stated above, I would already have 20+ fields in this table for the trainings alone. I wouldn't want to add an Issue and Expiration field for each one, that would triple the amount of fields I have. 

Any work around for this?

[TsiBiRu]

Hi @kpcollier,

I'm wondering why adding an Issue and Expiration date can triple the fields of your table.
 

If you can join these 20-30 pieces of training in one table, then you can just create a table named 'Training' where each training details is saved. After that, you can have a table called 'certification' where will serve as a conjunction table between your Training and certificate table.

In this table, we will be able to see all the training certificates of each employee.

Relationship ScreenShot

Datasheet View

 

And if these trainings have different or specific fields and can't be joined in a single table, what I can suggest is you create a table for each training(s) that can't be joined and create a different certification table for each one 

Screenshot of Relationship:

 

I've designed these tables to be normalized (3rd normal form), to avoid any data anomaly.

I hope this helps.

Regards,

TsiBiRu

 

[kpcollier]

Thanks Ruel, much appreciated. I followed your workflow and this is working great. I am now trying to create some kind of Report datapage that lists all of the Trainings on the top, all of the users on the left, and in my middle show expiration dates. I've created an example with Microsoft Excel, I'm just not sure how I would create this with a DataPage.

Thanks,

Keith

[kpcollier]

My coworker also wants to add a submission form that closely resembles the one I have attached here, with Employee Name and Number make up the top portion and the several different types of trainings on the lower portion. Next to each training he would like to be able to enter an issue date... I feel this makes it a lot more complex in terms of how I need to set up my table.

 

[TsiBiRu]

On 2/21/2019 at 10:14 AM, kpcollier said:

Thanks Ruel, much appreciated. I followed your workflow and this is working great. I am now trying to create some kind of Report datapage that lists all of the Trainings on the top, all of the users on the left, and in my middle show expiration dates. I've created an example with Microsoft Excel, I'm just not sure how I would create this with a DataPage.

Thanks,

Keith

Hi @kpcollier,

What you want to do will require you to create custom HTML tables to make the 'Traning Name' field as a column/header in your table, and it will also include extensive CSS and JS code as well.

What I did as a workaround, is I created a conjunction table to join the 'employee' and 'training ' table and I've used this new table as the Data source of my Tabular Report DataPage.

Then I've grouped the result by employee ID and I've made it collapsible as well, so in your report, you will see the names of the employee and if you want to see all the training certificate that they have. And its respective expiration date, all you have to do is to click on their name. I think this can actually improve the user experience because the user will not have a hard time reading the report accurately.

I've attached a copy of the DataPage that I've created so that you can see how it works.

I hope this helps.

Regards,

TsiBiRu

CaspioData_2019-Feb-26_1833.zip

[TsiBiRu]

On 2/22/2019 at 1:51 PM, kpcollier said:

My coworker also wants to add a submission form that closely resembles the one I have attached here, with Employee Name and Number make up the top portion and the several different types of trainings on the lower portion. Next to each training he would like to be able to enter an issue date... I feel this makes it a lot more complex in terms of how I need to set up my table.

 

Hi @kpcollier,

In my opinion, I don't think you need to make your table to be more complex than it has to be,  I think your coworker just want to add multiple certificates to one employee at one sitting.

What I did to achieve this without using custom coding and ensuring that the user experience will not be sacrificed is, I created a submission form to populate the certificate where the user can add a training certificate to one employee. Then  I've created a report DataPage that will show all the existing Certificate of the employee that the last certificate created belongs to, then I've enabled this to be edited or deleted. So that the user can easily delete or modify the records as he/she wants.

What I did is I deployed the submission  DataPage on one webpage, then I've set it to redirect to a second webpage where the same submission DataPage is deployed to allow the user to add more records. And on this webpage, I've also deployed the report DataPage that will display all the existing certificate of the employee that the last certificate create belongs to.

So that you can see how it works, here is the link where you can try, please note that I will be removing this link after 1 week.

I've also attached the exported copy  of the DataPages that I've worked with so that you can see how I've implemented this workflow

I hope this helps.

Regards,

TsiBiRu

CaspioData_2019-Feb-26_2236.zip

[kpcollier]

Thanks Ruel, I have downloaded the example files you posted and will try this out. It looks pretty neat and well done, very nice! I appreciate your help with all of this. You can remove the link and example page on your website.

 

[kpcollier]

Hey @TsiBiRu, could you show me how to add a search box to this app? What I was thinking was, use the search box to find the records, the name is search is used for the submission form and has same functionality as before, you add the training and it shows in the report. I just want to add a search box to make it a little easier to just view the records.

Let me know if this is possible, thanks!

[TsiBiRu]

Hi @kpcollier,

Yes, that is possible. I was able to create a solution for this using some custom code.

What I did is, I added a button that will pass the ID of the selected employee on the drop-down menu to a pop-up window, where I've deployed a report DataPage that will be displaying the existing certificate of the selected employee.

 

 

I've attached the exported copy of the Datapages that I've worked with, you can import this to your account to see how it works.

I think you will need to redeploy the report DataPage, and copy the Deploy URL and update the JS code for this to work on your end.

I hope this helps.

Regards,

TsiBiRu

 

CaspioData_2019-Mar-06_1721.zip