JSON converter for Access (1 Viewer)

[SHANEMAC51]

when thinking about table structure, there is quite a bit of redundancy if I went with the traditional RDBMS approach to table structure

additionally, you will need several reference books for entering text fields
1 people checking events
2 the person who signs the documents
3- templates for unifying the input of object names
4 typical comments (you will also need reports on them)

 

[Zydeceltico]

I used Requirement Number to give users a chance to number their requirements in the order they want. You may want certain inspection requirement to occur before another so you can order them any way you wish and they will be sorted in that order on the form.

I use the Selection field in each table so that if you use continuous forms where all records can be viewed at one time with scroll bars, then each record can be selected individually which is important when adding new records. If you use single forms where each record is displayed individually, then you would not use the Selection field. For example, using continuous forms, you can view all

RequirementSelection and all Selection fields I use as Yes/No with a Checkbox control on the form. Requirement number is just a number field users can enter numbers to use to order their Requirements.

I am so sorry but my WiFi service failed this morning and the tech just repaired it. My internet connection obviously was disconnected. I am working on a model with forms so you can see what it look like. I am not using continuous forms so some of the Selection fields are not going to be used. I'll attach the file tomorrow so you can play with it. It's really not that complicated, since I've done it before.

Of course you can add fields where you need them.

Thank you Larry! Looking forward to it.

 

[Zydeceltico]

Here's a picture of a form that uses an EAV design. This is an application that is used to produce all the documents required to issue an insurance policy. Each type of policy has different data requirements. Hence the EAV design. So the fixed properties are columns as you would normally expect but the other columns vary by policy type. When you create a new record for a customer, the policy type combo determines what fields are required and they are all appended in the afterUpdate event of the main form. This particular app is very flexible and is controlled by the user rather than the developer. I designed it so that if they started issuing a new type of policy and needed to add new data fields, they could. In your case, the data fields are fixed unless you get something new but it's probably something they want the developer to do for them than for them to do for themselves.

In order to handle the "required" aspect of data, it is the Word automation part that pulls all the EAV data for use in a word document and as each record is read, its properties are checked and if it is required for this document, then the value of the field must be not null.

If you want more details, I can go into the supporting tables if you want to go this way.
View attachment 98955

Hi Pat,

Actually, the data fields would not necessarily be fixed on the front end but defined on the backend - which I think is the same as what you are describing. The analogy is definitely accurate. I would really appreciate seeing the supporting table structure.

 

[Zydeceltico]

Just a little nit pick... JSON is not a data type, just like XML or HTML is not a data type either. Rather, it's just another way to mark up or notate an otherwise plain text information into some sort of a structure.

You may also have the wrong idea about how JSON is used on the web. Rather than store data in JSON format, websites actually store the data in a database table or tables and simply use JSON to "share" that data with other systems or users.

So, if I was going to integrate with another (external) system that accepts JSON data, I would probably store that information in tables and then export them into a JSON file. Similarly, when I receive JSON data from outside sources, I would probably parse them and store them in my normalized tables.

Just my 2 cents...

This is the article that really got me thinking (for any interested parties) - Microsoft JSON article - and again - driven by a need to find a means of circumventing the need for Access or Runtime on many computers for both input and output. I would use a browser instead of an Access FE - at least in my dream-world I would do that. ....and yes - I know the article is about SQL Server (which this company's ERP is built on so they are less hesitant with considering some other approach.

I see exactly what you are saying about parsing in and out.

 

[Pat Hartman]

I removed all tables that are not specifically related to the EAV definition. I also removed the endorsement tables which are also EAV but would just complicate the definition.
-- data
tblVariableDataHeader = the header for the policy.
tblVariableDataValues = the data for the EVA fields. One row per field
--- definition
tblProduct = the header for each product type
tblField = all the variable fields. Field types are -- "Text";"Date";"Currency";"Number";"Percent";"Memo". ControlTypes are -- "Combo";"TextBox";"Checkbox". ComboDataSourceName = the name of the query to populate the RowSource of the combo.
tblProductFields = the fields associated with a particular product. This is the source of the append query. When a policy is being quoted, the variableDataHeader is created and the ProductID is used in the append query that copies the tblProductField definitions to tblVariableDataValues where they will eventually be filled.
tblDocument = the documents associated with a product.
tblDocumentFields = the fields associated with a document.

To add a new policy type the user would do the following:
1. Add row to tblproduct
2. Add new fields to tblField
3. Add fields to tblProductFields - copy options are available to reduce data entry
4. Create necessary Word documents with bookmarks
5. Add documents to tblDocument
6. Add fields to DocumentFields
7. Ask me to create any new queries needed for the combo lists. I couldn't let the user create database objects. If I were to do it again, I would put these queries in a table rather than making them permanent and then create them as temporary querydefs on the fly.

 

[gemma-the-husky]

This is the article that really got me thinking (for any interested parties) - Microsoft JSON article - and again - driven by a need to find a means of circumventing the need for Access or Runtime on many computers for both input and output. I would use a browser instead of an Access FE - at least in my dream-world I would do that. ....and yes - I know the article is about SQL Server (which this company's ERP is built on so they are less hesitant with considering some other approach.

I see exactly what you are saying about parsing in and out.

Would you use a browser? I don't really follow. As far as I am concerned, XML and JSON aren't really there to be read by users, any more than csv's or fixed width text files . They are all just mechanisms to facilitate sharing data between IT systems.

You need something like a database to convert these messages into a form that is more easily assimilated by end users.

 

[LarryE]

If you want to use ACCESS, I had something like these in mind for input forms and a very simple report. This design uses both continuous forms and single forms. When you add new inspection dates and times for any inspection type, it automatically appends new related records from the inspection detail form and the requirements form. Maybe you can use this design or a similar one. Of course you would add fields where necessary for your own needs. I can attach the .accdb file if you wish to look at it. Its incomplete though for your needs.



I included the time of day in case some "items" could be inspected more than once a day. I don't know if you need a table to hold individual "Items" (whatever they are) that are inspected, but that can be done as well.

 

[Zydeceltico]

If you want to use ACCESS, I had something like these in mind for input forms and a very simple report. This design uses both continuous forms and single forms. When you add new inspection dates and times for any inspection type, it automatically appends new related records from the inspection detail form and the requirements form. Maybe you can use this design or a similar one. Of course you would add fields where necessary for your own needs. I can attach the .accdb file if you wish to look at it. Its incomplete though for your needs.
View attachment 98986
View attachment 98987
View attachment 98988
I included the time of day in case some "items" could be inspected more than once a day. I don't know if you need a table to hold individual "Items" (whatever they are) that are inspected, but that can be done as well.

Larry, that approach would work very well for me actually. Yes - if you don't mind sending the file that would be great to use as a template or at least a skeleton to build on/from. I am really grateful for this. Thank you!

 

[LarryE]

Larry, that approach would work very well for me actually. Yes - if you don't mind sending the file that would be great to use as a template or at least a skeleton to build on/from. I am really grateful for this. Thank you!

Sure. Here it is. NEEDS TESTING!!!