Array Question

[timquinn]

Hi guys,

I'm trying to convert a very large spreadsheet into a form. It's about 200 rows with about 10 corresponding columns. In normal programmatic sense I'd look into creating an array to store this data so that I'd be creating 200 fields instead of 2000. However, with the array entity type I think that I must be missing something... I'm allowed to manually enter in a list of what I assumed were elements of the array but in a practical sense the end result is effectively a lookup list.

To give a little more color, this is for an annual review of our technology clients. The table would look something like below with double the columns and 198 more rows.

We have a guy doing this right now, but it all lives in spreadsheets which are difficult to reference once they're used and carry with them no historical context.

ITEM                           STATUS                        RISK                          SOLUTION                   GRADE                 FIX ESTIMATE
Server Health              <text>                           <text>                         <text>                           <enum>                 <int>
Backups                       <text>                           <text>                         <text>                           <enum>                 <int>

[Maximus]

Hi,
Could you explain more explicitly? Are you trying to make something like a report, or something like that? Please shed the light on what you are trying to achieve.

[timquinn]

Yes - the goal here is to ultimately produce a report. Right now we have the proverbial spreadsheet with 200 rows for each client. While I've gotten around the short-term by summarizing those rows in Espo fields (which frankly may be "good enough") ideally I'd love the ability to, in the future, store data in a standardized way in arrach format.

For example, our engineers will do a manual health check on every server. Rather than create fields for each server's Status, Grade, Risk and Recommendation it would be great to handle that a bit more programmatically. Repeating tables, for example, would do the trick.

[Maximus]

From your description, I assume that you need something to be able to create a report. Do you familiar with our Report feature in the Advanced Pack. Please feel free to try it here https://demo.espocrm.com/#Report.

[timquinn]

For actual reporting I'm fine. We poll the MySQL database with a number of tools which we've been using for awhile (PowerBI, Crystal). Maybe I just can't get my head around it -- let's say I have a client with multiple locations and I want to add 11 locations for one client and 17 for another client. How would I programmatically add that information at the field level versus creating tons of entities in the relationship panels with separate forms? Typically that would be handled with an array to assure we had a scalable data management methodology behin the scenes. Things like Microsoft Infopath did that with repeating tables and forms. I'm OK getting prgramatic if this will involve creating seperate templates and views.

[telecastg]

Assuming that the number of columns in the spreadsheet is always the same, perhaps treating each diagnostic as an array of json objects like this would do it:

"Item" : [
{
"Status": "anyvalue",
"Risk": "anyvalue",
"Solution": "anyvalue",
etc
}
]

If you don't mind doing some coding, to store this structure you would have to create a custom field (for example "Diagnostic") where the information would actually be stored in a text field inside the MySql table.

To guide you, check the code for this free custom field plugin, it is a dynamic checklist field that allows you to add as many "items" as you want and has two "columns" a checkbox value and a label value.

https://github.com/EspoCRM-Custom-Modules/Dynamic-Checklist-Field

The checklist information is stored as a text MySql field with the same structure:
[
{
"label": "description of todo item",
"state": true or false
}
]

In your case you would just have to develop a field that has as many "columns" as your spreadsheet and add that field to a "Location" entity which would be linked in a Many-To-One relationship to your Customer.

If you end up implementing this, PLEASE share your code so everybody else benefits from our common efforts.

Cheers