Hello everyone,

I am currently working on a web app that is to be used to receive reports from external counterparts. Depending on the specific contract and the type of report, the format of these (Excel) reports vary. To make matters even more challenging is that different entities have to be mapped depending on the report type submitted and foreign keys determined based on business keys. I have used the Generic import files forge component to extract column index, line index and value for the individual cells. From this I would like to do a mapping using template definitions where I associate a given column for a given report with a given entity and entity attribute. I have mapped this using the Entity and Entity_attr fields in the System component.

One I had done this, I tried to figure out how to use this data to build structures that I can use for insertion into the final tables. I have not been able to find any solution in the documentation or the forum. Is this possible at all? I could do the mapping by calling different Server Actions depending on the report format, but that reduce reusability and wouldn't allow end users to define new report formats through an administration screen. Is there some other obvious solution that I have missed?

Unfortunately, I cannot share the file as it contains quite a bit too much sensitive data, so I hope I have made the issue clear.

Hi Mattias,

Just to make it more clear, you were able to get the data from the Excel into the Record List? And you can know where to map the columns right?

So if all of this is correct you just need to use the Create actions available on the entities you want and map to the right attributes on the source of that action.


Hope this help. 

Hi Nuno!

Thanks for your reply!

I see that I haven't outlined my problem clearly enough. I have loaded into a temporary entity where each item in the Excel list is stored together with row and column index from the Excel file. The complication is that a given column will map differently depending on the report template used. Excel to record list wouldn't work as using headers to identify columns is too error prone.

I have also created a report and report column definition entity where a column index maps to a specific Entity.id and Entity_Attr.id. With these values I can derive which entity and attribute name that should be used. In dynamic sorts we can use text strings to refer to specific entities and attributes with the syntax {<entity name>}.[<attribute name>]. The question is if that format could be used in other actions. 

Example:

Say I have an entity, E, with three attributes, [A1, A2, A3]. In one report I have to map columns [C1, C2, C3 ]as [C1=A1, C2=A2, C3=A3]. In a second report the mapping is [C1=A2, C2=A3, C3=A1]. I can store a mapping table identifying the column numbers and the corresponding attributes. But how to use this is more of a challenge to me as I cannot find a way to reference the different parts of the structure in E1 dynamically.


I hope this makes my issue a bit clearer.

Solution

Hi,

In fact, it's a bit confusing or I'm really not getting the point or I would do in a different way.

Trying to go straight to the point. 

As you mention there are actions such as the sort that works with this {<entity name>}.[<attribute name>] but most of them don't so you will need to build your own actions for that.

Solution

Thank you for your response, Nuno!

I think that I could have been more clear and I start to get a feeling that I am over engineering the solution I am working on (which in turn is just a POC anyway).

That being said, I would be interested in how one can solve the general problem of automating data validation and mapping so that it can be parametrized by end users and then applied to a given input file.

I see several potential use cases for such functionality and I am fairly sure many have found ways to implement this using Outsystems.

I will let the matter rest for the time being as I have some less elegant solutions for it that I can easily build and I have to meet deadlines =)

When I have some time, I will open a thread on the topic in general where I explain the principles of what I am trying to achieve a bit more clearly.

Once again, thanks for taking your time!