How to return a value with variable entity attribute or add matrix arrays?

How to return a value with variable entity attribute or add matrix arrays?

  

Hoping someone can help.


I'm looking to create a zoning structure that is based on postcodes. So in my app I will have ~16000 suburbs that will have a zone attached to it. So I would intend to set the 16000 suburbs up as records in a structure, then I would probably set up new attributes for each companies zoning.


So what I need to do is pull a value based a variable attribute. How is this possible?

or

is there another way to add matrix arrays to outsystems (i can't see anywhere). If i were to put it into a excel example I would make my array based on 16000 rows and 3,4,5,6,7... columns based on how many different companies I had in my system (this will grow over time), then do a combination HLOOKUP, VLOOKUP to search through the array. Is there a way to replicate a similar idea in outsystems? 

Hi Talis,

Instead of an array, you can use a static entity for the zipcodes/suburbs and use a table that has the company specific data per company. (id, zipcodeid, companyid, field1, field2, fieldN)

Kind regards,

Remco Dekkinga

HI Remco,


Thanks for the reply!

However I'm a little confused by your statement. I think what you're suggesting is adding the 16000+ suburbs as records in a static entity, then making each new company as an entity attribute. Is this the case?

I think I must have misunderstood; as this solution would take me months to enter all the suburbs, assuming Outsystems could handle as many records as that. Would you mind clarifying? 

Talis -

I suggest that you bootstrap the records from an Excel spreadsheet. Easiest way to do this is to right-click Entities and choose "Import from Excel", it will analyze your Excel file, create an Entity based on it, import the Excel file as a resource, and have a "When Published" timer to extract the records from the spreadsheet and put them into the system. Once you do that, you can build screens on the Entity to maintain your zones.

J.Ja

Justin James wrote:

Talis -

I suggest that you bootstrap the records from an Excel spreadsheet. Easiest way to do this is to right-click Entities and choose "Import from Excel", it will analyze your Excel file, create an Entity based on it, import the Excel file as a resource, and have a "When Published" timer to extract the records from the spreadsheet and put them into the system. Once you do that, you can build screens on the Entity to maintain your zones.

J.Ja

Hi Justing,


Thanks for the response, that was my initial thought too. However were the complication lies in that solution is; once I have this bootstrapped and let's say I have the attributes: ID, suburb, company A, company B, Company C. I can easily filter in an aggregate to find a suburb based on an input (and that will return the zones for company A,B and C), but they how do i filter across through attributes based on variable companies.  Normally I would return a fixed value such as "entities.zoning_list.companyA". How do I make that last part (companyA) variable between company B, company C etc.


Talis Krumins wrote:

Justin James wrote:

Talis -

I suggest that you bootstrap the records from an Excel spreadsheet. Easiest way to do this is to right-click Entities and choose "Import from Excel", it will analyze your Excel file, create an Entity based on it, import the Excel file as a resource, and have a "When Published" timer to extract the records from the spreadsheet and put them into the system. Once you do that, you can build screens on the Entity to maintain your zones.

J.Ja

Hi Justing,


Thanks for the response, that was my initial thought too. However were the complication lies in that solution is; once I have this bootstrapped and let's say I have the attributes: ID, suburb, company A, company B, Company C. I can easily filter in an aggregate to find a suburb based on an input (and that will return the zones for company A,B and C), but they how do i filter across through attributes based on variable companies.  Normally I would return a fixed value such as "entities.zoning_list.companyA". How do I make that last part (companyA) variable between company B, company C etc.



OK, so your spreadsheet (and your SuburbImport Structure) should look like:

* Suburb

* Company (A, B, or C)

* Other needed columns

Then you have a Static Entity for Company, with the values:

* A

* B

* C

Then you have your Suburb entity:

* ID

* Suburb

* CompanyId

* Other needed Attributes

On your import, as you loop through, for each row in the spreadsheet, lookup the Company record with the value (so filter like... "Company.Name = ExcelToRecordList.Current.SuburbImport.Company") to get the ID to put into the Suburb record, and then save the Suburb record.

Then you can filter Suburb on your screen with "Suburb.CompanyId = Entities.Company.A" or whatever.

Make sense?

J.Ja

Justin James wrote:

Talis Krumins wrote:

Justin James wrote:

Talis -

I suggest that you bootstrap the records from an Excel spreadsheet. Easiest way to do this is to right-click Entities and choose "Import from Excel", it will analyze your Excel file, create an Entity based on it, import the Excel file as a resource, and have a "When Published" timer to extract the records from the spreadsheet and put them into the system. Once you do that, you can build screens on the Entity to maintain your zones.

J.Ja

Hi Justing,


Thanks for the response, that was my initial thought too. However were the complication lies in that solution is; once I have this bootstrapped and let's say I have the attributes: ID, suburb, company A, company B, Company C. I can easily filter in an aggregate to find a suburb based on an input (and that will return the zones for company A,B and C), but they how do i filter across through attributes based on variable companies.  Normally I would return a fixed value such as "entities.zoning_list.companyA". How do I make that last part (companyA) variable between company B, company C etc.



OK, so your spreadsheet (and your SuburbImport Structure) should look like:

* Suburb

* Company (A, B, or C)

* Other needed columns

Then you have a Static Entity for Company, with the values:

* A

* B

* C

Then you have your Suburb entity:

* ID

* Suburb

* CompanyId

* Other needed Attributes

On your import, as you loop through, for each row in the spreadsheet, lookup the Company record with the value (so filter like... "Company.Name = ExcelToRecordList.Current.SuburbImport.Company") to get the ID to put into the Suburb record, and then save the Suburb record.

Then you can filter Suburb on your screen with "Suburb.CompanyId = Entities.Company.A" or whatever.

Make sense?

J.Ja

Thanks again for the reply.


I think we might have slightly different ideas about what is trying to be accomplished here. Basically what I'm trying to do is look down a list which I have bootstrapped, for this part I would filter on the suburb, then I need to look across the list a return the value in a varying column. For instance, a person might buy a product from company A, so I need to know company A's delivery zoning, another time the might get a product from company B, so I would return the company B value to the same spot.


I have already bootstrapped the attached spreadsheet to Outsystems, now I need to somehow return different column values each time. (sorry if this is what you're saying, but if you are you might just need to dumb it down a bit for me)



HI,

I'm probably not understanding the problem.

As far as I was able to understand:

- create a suburb entity (SuburbId + all suburb specific attributes)

- create a DistributionCompany entity (DistributionCompanyID + all company specific attributes)

- create a N - N SuburbDistributionCompany relationship entity ( SuburbID + DistributionCompanyID + all suburb vs company specific attributes)

To answer "a person might buy a product from company A, so I need to know company A's delivery zoning"

SELECT suburb.* 

FROM suburb inner join SuburbDistributionCompany subcomp

  on suburb.id = subcomp.suburbId

inner join DistributionCompany comp

  on subcomp.DistributionCompanyID = comp.id

WHERE

  comp.code = XXX (or however you identify a company)


"another time the might get a product from company B, so I would return the company B value to the same spot"

I didn't understood this requirement 




Tilas -

This is not a good database design. It does not scale well if you have to add more and more companies, and it is difficult to program against. I suggest that you move to a move to a design where "what company?" is a lookup to a Static Entity, as I suggest. That will make EVERYTHING easier for you.

J.Ja

Justin James wrote:

Tilas -

This is not a good database design. It does not scale well if you have to add more and more companies, and it is difficult to program against. I suggest that you move to a move to a design where "what company?" is a lookup to a Static Entity, as I suggest. That will make EVERYTHING easier for you.

J.Ja


Hi Justin,


Is there any chance you could give me a small mock up in an OML, I'm sure what you're saying makes sense to most, but I just can't quite follow your logic of what you mean. This way it'll be much easier for me to see what you mean. 

Talis -

Sorry, I really don't have the time to put into sample code at the moment. If you send some code I might be able to tweak it so it works.

J.Ja