Bootstrap To Multipe Entities

I have the following Customers and Jobsites.  A Customer can have many Contacts and many Jobsites so my data model is 

Contacts Main

Id / ContactName / Address1/Address2/City/State/Zipcode/Country/ContactType/ContactClass/ReffSource

With the follwing tables linked one to many from this table

Contacts

Id/ContactMainId(Fk)/FirstName/LastName/Phone1/Phone2/Email/......

Locations

Id/ContactsMainId(Fk)/LocationName/Address1/Address2/City/State/Zipcode.....

The data that I have comes from existing MS Access Database which has worked in the past like this

Contacts

Id/ContactName/Address1/Address2/City/State/Zipcode/Country/FirstName/LastName/ContactType/ContactClass/ProjectAddress1/ProjectAddress2/ProjectCity/ProjectState/ProjectZipcode/ProjectCountry/PARENTID (Recursive)/RefferedBy(Recursive)

When New Jobsites are added many fields are left blank in this situation same happens with Alt Contacts. In Access when a call comes in everything is on one form. There is a checkbox that copies Main address to jobsite address if they are the same... Example a homeowner gets mail to their home and jobsite is also home.  

What is the correct Data Model?  And If its better to have things in diff tables like first example how do I bootstrap?


Hello Rob, 

I see you have tried to normalise the original table Contacts from your MS Access database to multiple table to avoid duplication of the data which is the right approach. 

I think your data model can further be normalised. Please see below as reference, I have normalised the data as well as segregated the data as per business terms 

With regards to bootstrap, you want to be able to read all the data from one table and flesh it out to the multiple tables.You can export data from MS Access to spreadsheet. You can bootstrap the single spreadsheet onto one of the table using the built-in bootstrap feature which will insert data in one table. You can go update the auto generated server action to create entries on Multiple tables.


Thanks,

Babu

Hi Rob,

Babu is correct, there is an automatically generated bootstrapping feature.

However this does not handle adding foreign keys, but you can use it as a starting point and make the required changes yourself.

The issues are

  • Foreign keys must be added to entities.
  • Identify the record for applying foreign keys, eg linking a Location to a Contact Main, and
  • Ensure the bootstrap of entities occurs in the right order.  This is so that a Contact Main record exists when attempting to link to it from a Location or Contact record.

Add foreign keys

After generating the bootstrapping for each entity, you will need to link them by adding foreign keys. eg add ContactMainId to Contact and Location entities.

You will have used an identifier for the contact in the Contact and Location spreadsheets.  This will have been added as an attribute of those entities.  So you should remove this attribute as the foreign key takes it's place.

Identify the record

Choose an identifying attribute of Contacts Main. You may use the Id, or you might not want to store the Id from the Access database, so if the ContactName is unique, that could be an option.

When importing a Location or Contact, lookup the Contact Main record and assign the foreign key.

This will require that the Location and Contact spreadsheets have your chosen Contacts Main identifier in one of its columns in order to provide it to the lookup.

Ensure the right order

There are two options here; run the bootstrap manually, or trigger all the bootstraps from a single timer.

The generated bootstrap will set the timer to be run on publish, to run them manually, just remove the schedule altogether for the bootstrap timer(s).

By triggering them all from a single timer, you can ensure they run in the right order. There is an action associated with each timer.  You can delete the timer and the action will remain.  Delete all but one of the timers.  Drag the other actions into the one remaining in the order in which they should run.

I hope this helps.

Kind regards,

Stuart


Babu Basha wrote:

Hello Rob, 

I see you have tried to normalise the original table Contacts from your MS Access database to multiple table to avoid duplication of the data which is the right approach. 

I think your data model can further be normalised. Please see below as reference, I have normalised the data as well as segregated the data as per business terms 

With regards to bootstrap, you want to be able to read all the data from one table and flesh it out to the multiple tables.You can export data from MS Access to spreadsheet. You can bootstrap the single spreadsheet onto one of the table using the built-in bootstrap feature which will insert data in one table. You can go update the auto generated server action to create entries on Multiple tables.



Thanks,

Babu


Thank You Babu!

I am just seeing this as I have multiple emails addresses.   Another question.   How do I create one form having inputs to both the one and many sides of this relationship?   Offfice would need simple form that they can use without having to create records in multiple table and maybe forget something.  I have designed the form  just need to get sorted out into proper tables.


Stuart Harris wrote:

Hi Rob,

Babu is correct, there is an automatically generated bootstrapping feature.

However this does not handle adding foreign keys, but you can use it as a starting point and make the required changes yourself.

The issues are

  • Foreign keys must be added to entities.
  • Identify the record for applying foreign keys, eg linking a Location to a Contact Main, and
  • Ensure the bootstrap of entities occurs in the right order.  This is so that a Contact Main record exists when attempting to link to it from a Location or Contact record.

Add foreign keys

After generating the bootstrapping for each entity, you will need to link them by adding foreign keys. eg add ContactMainId to Contact and Location entities.

You will have used an identifier for the contact in the Contact and Location spreadsheets.  This will have been added as an attribute of those entities.  So you should remove this attribute as the foreign key takes it's place.

Identify the record

Choose an identifying attribute of Contacts Main. You may use the Id, or you might not want to store the Id from the Access database, so if the ContactName is unique, that could be an option.

When importing a Location or Contact, lookup the Contact Main record and assign the foreign key.

This will require that the Location and Contact spreadsheets have your chosen Contacts Main identifier in one of its columns in order to provide it to the lookup.

Ensure the right order

There are two options here; run the bootstrap manually, or trigger all the bootstraps from a single timer.

The generated bootstrap will set the timer to be run on publish, to run them manually, just remove the schedule altogether for the bootstrap timer(s).

By triggering them all from a single timer, you can ensure they run in the right order. There is an action associated with each timer.  You can delete the timer and the action will remain.  Delete all but one of the timers.  Drag the other actions into the one remaining in the order in which they should run.

I hope this helps.

Kind regards,

Stuart


Stuart,

Thanks for getting back to me!  Just another question.  So using the same methods I can have a form w fields from both the one and the manys and modify save action to place in seperate entities?

Thanks

Rob Rough wrote:

Babu Basha wrote:

Hello Rob, 

I see you have tried to normalise the original table Contacts from your MS Access database to multiple table to avoid duplication of the data which is the right approach. 

I think your data model can further be normalised. Please see below as reference, I have normalised the data as well as segregated the data as per business terms 

With regards to bootstrap, you want to be able to read all the data from one table and flesh it out to the multiple tables.You can export data from MS Access to spreadsheet. You can bootstrap the single spreadsheet onto one of the table using the built-in bootstrap feature which will insert data in one table. You can go update the auto generated server action to create entries on Multiple tables.



Thanks,

Babu


Thank You Babu!

I am just seeing this as I have multiple emails addresses.   Another question.   How do I create one form having inputs to both the one and many sides of this relationship?   Offfice would need simple form that they can use without having to create records in multiple table and maybe forget something.  I have designed the form  just need to get sorted out into proper tables.


Babu,  

One more thought.  Estimates will be created using these addresses.  Mailing address and project location will be fields in the estimate.   I dont want the address to ever change on an estimate after it is created so this adds some compexity to my model.  How do i get around that?

Thanks Again!


Rob Rough wrote:

Stuart Harris wrote:

Hi Rob,

Babu is correct, there is an automatically generated bootstrapping feature.

However this does not handle adding foreign keys, but you can use it as a starting point and make the required changes yourself.

The issues are

  • Foreign keys must be added to entities.
  • Identify the record for applying foreign keys, eg linking a Location to a Contact Main, and
  • Ensure the bootstrap of entities occurs in the right order.  This is so that a Contact Main record exists when attempting to link to it from a Location or Contact record.

Add foreign keys

After generating the bootstrapping for each entity, you will need to link them by adding foreign keys. eg add ContactMainId to Contact and Location entities.

You will have used an identifier for the contact in the Contact and Location spreadsheets.  This will have been added as an attribute of those entities.  So you should remove this attribute as the foreign key takes it's place.

Identify the record

Choose an identifying attribute of Contacts Main. You may use the Id, or you might not want to store the Id from the Access database, so if the ContactName is unique, that could be an option.

When importing a Location or Contact, lookup the Contact Main record and assign the foreign key.

This will require that the Location and Contact spreadsheets have your chosen Contacts Main identifier in one of its columns in order to provide it to the lookup.

Ensure the right order

There are two options here; run the bootstrap manually, or trigger all the bootstraps from a single timer.

The generated bootstrap will set the timer to be run on publish, to run them manually, just remove the schedule altogether for the bootstrap timer(s).

By triggering them all from a single timer, you can ensure they run in the right order. There is an action associated with each timer.  You can delete the timer and the action will remain.  Delete all but one of the timers.  Drag the other actions into the one remaining in the order in which they should run.

I hope this helps.

Kind regards,

Stuart


Stuart,

Thanks for getting back to me!  Just another question.  So using the same methods I can have a form w fields from both the one and the manys and modify save action to place in seperate entities?

Thanks

Hi Rob, yes you can use a single for to edit and save the whole lot.  However, OutSystems accelerators do not help with this, you have to build the form yourself and construct your lists of items.

To start off with, the best way is to use a combination of screens to achieve this, just editing a single entity at a time.  Then when you are comfortable with how OutSystems forms work, and if you need to, start building a form to edit multiple and parent child relationships.


Rob Rough wrote:

Babu Basha wrote:

Hello Rob, 

I see you have tried to normalise the original table Contacts from your MS Access database to multiple table to avoid duplication of the data which is the right approach. 

I think your data model can further be normalised. Please see below as reference, I have normalised the data as well as segregated the data as per business terms 

With regards to bootstrap, you want to be able to read all the data from one table and flesh it out to the multiple tables.You can export data from MS Access to spreadsheet. You can bootstrap the single spreadsheet onto one of the table using the built-in bootstrap feature which will insert data in one table. You can go update the auto generated server action to create entries on Multiple tables.



Thanks,

Babu


Thank You Babu!

I am just seeing this as I have multiple emails addresses.   Another question.   How do I create one form having inputs to both the one and many sides of this relationship?   Offfice would need simple form that they can use without having to create records in multiple table and maybe forget something.  I have designed the form  just need to get sorted out into proper tables.


Babu,

I have seen the light :)  Thanks


Stuart Harris wrote:

Rob Rough wrote:

Stuart Harris wrote:

Hi Rob,

Babu is correct, there is an automatically generated bootstrapping feature.

However this does not handle adding foreign keys, but you can use it as a starting point and make the required changes yourself.

The issues are

  • Foreign keys must be added to entities.
  • Identify the record for applying foreign keys, eg linking a Location to a Contact Main, and
  • Ensure the bootstrap of entities occurs in the right order.  This is so that a Contact Main record exists when attempting to link to it from a Location or Contact record.

Add foreign keys

After generating the bootstrapping for each entity, you will need to link them by adding foreign keys. eg add ContactMainId to Contact and Location entities.

You will have used an identifier for the contact in the Contact and Location spreadsheets.  This will have been added as an attribute of those entities.  So you should remove this attribute as the foreign key takes it's place.

Identify the record

Choose an identifying attribute of Contacts Main. You may use the Id, or you might not want to store the Id from the Access database, so if the ContactName is unique, that could be an option.

When importing a Location or Contact, lookup the Contact Main record and assign the foreign key.

This will require that the Location and Contact spreadsheets have your chosen Contacts Main identifier in one of its columns in order to provide it to the lookup.

Ensure the right order

There are two options here; run the bootstrap manually, or trigger all the bootstraps from a single timer.

The generated bootstrap will set the timer to be run on publish, to run them manually, just remove the schedule altogether for the bootstrap timer(s).

By triggering them all from a single timer, you can ensure they run in the right order. There is an action associated with each timer.  You can delete the timer and the action will remain.  Delete all but one of the timers.  Drag the other actions into the one remaining in the order in which they should run.

I hope this helps.

Kind regards,

Stuart


Stuart,

Thanks for getting back to me!  Just another question.  So using the same methods I can have a form w fields from both the one and the manys and modify save action to place in seperate entities?

Thanks

Hi Rob, yes you can use a single for to edit and save the whole lot.  However, OutSystems accelerators do not help with this, you have to build the form yourself and construct your lists of items.

To start off with, the best way is to use a combination of screens to achieve this, just editing a single entity at a time.  Then when you are comfortable with how OutSystems forms work, and if you need to, start building a form to edit multiple and parent child relationships.


Stuart,

Thank you for your help.