Best practices to enforce 1:1 relationships?

Hi Francesca Shiekh,

The first answer comes to my mind is the logic hook as you mentioned. However, It would be good to know your use case. 

Tevfik Tümer
Sr. Developer Support Engineer 

Thank you Tevfik Tümer ,

we have a 1:1 between Quotes and Cases.

Cases are created to let our CS team know that the order is ready to enter.

Our Sales team creates a Case to process an Order in our ERP system for a given a Quote, and attaches the customer PO to the Case, they link the Case to the Quote.

We want to make sure that there is only one Case per Quote and only one Quote per Case, and if someone tries to enter a second Case for that same quote, or a second Quote for the same Case, we want to stop them from doing so and alert them to the fact that there is already a Case for that Quote.

I added a 1:1 Cases-Quotes.

Example of the non enforcement:

CaseA, relate to QuoteA => The related CaseA shows on the QuoteA, the related QuoteA shows on the CaseA

CaseB relate to QuoteA => CaseB shows QuoteA, QuoteA shows CaseB, CaseA has lost the relationship and is left without a related Quote and there was no warning that the relationship between CaseA and QuoteA was being removed.

To me, that's a big problem as other Sugar customers in similar scenarios may be losing data.
The logical expectation of a 1:1 is that it would be enforced by the code with a warning that proceeding will destroy the previously exising relationship.

Thank you,

Francesca

Thank you Tevfik Tümer ,

we have a 1:1 between Quotes and Cases.

Cases are created to let our CS team know that the order is ready to enter.

Our Sales team creates a Case to process an Order in our ERP system for a given a Quote, and attaches the customer PO to the Case, they link the Case to the Quote.

We want to make sure that there is only one Case per Quote and only one Quote per Case, and if someone tries to enter a second Case for that same quote, or a second Quote for the same Case, we want to stop them from doing so and alert them to the fact that there is already a Case for that Quote.

I added a 1:1 Cases-Quotes.

Example of the non enforcement:

CaseA, relate to QuoteA => The related CaseA shows on the QuoteA, the related QuoteA shows on the CaseA

CaseB relate to QuoteA => CaseB shows QuoteA, QuoteA shows CaseB, CaseA has lost the relationship and is left without a related Quote and there was no warning that the relationship between CaseA and QuoteA was being removed.

To me, that's a big problem as other Sugar customers in similar scenarios may be losing data.
The logical expectation of a 1:1 is that it would be enforced by the code with a warning that proceeding will destroy the previously exising relationship.

Thank you,

Francesca

Hi Francesca Shiekh ,

I think the current behavior is valid as there are reasons for changing a 1:1 relationship and there also isn't a configuration to say "this relationship should only be changeable from one side and not the other".

With that said, you can make this enforceable with a combination of SugarLogic and SugarBPM with the following steps:

1. Create a field on the Quotes module (e.g. case_related_c) which could be a checkbox, text field, etc. For my example, I used a text field.
2. Create a SugarBPM definition on the Quotes module with the following elements:
   1. Start Event: Occurs on 'Relationship Change' with criteria of 'Cases Added'
   2. Action: Change your field created in step 1 to have a value (e.g. 'Yes')
   3. End Event
      
      This ensures that your custom field gets updated 
3. Create a field on the Cases module (e.g. quote_saved_c) which could be a checkbox, text field, etc. For my example, I used a checkbox field.
4. Create a SugarBPM definition on the Cases module with the following elements:
   1. Start Event: Occurs on 'Relationship Change' with criteria of 'Quotes Added'
   2. Action: Change your field created in step 3 to have a value (e.g. checked)
   3. End Event
5. Create a text field on the Cases module (e.g. quote_validation_c). The field should have the following properties:
   1. Required
   2. Calculated with the following formula:
      

      ifElse(
          and(
              equal(
                  related(
                      $cases_quotes_1,
                      "description"
                  ),
                  "Case"
              ),
              equal(
                  $quote_saved_c,
                  0
              )
          ),
          "",
          "Valid"
      )

      
      The formula will only output a value if the related quote has not gone through the SugarBPM process (i.e. related to another case). If the quote has gone through the SugarBPM process, then the field will be blank and since the field is required, it will prevent the user from saving the case assuming you have this field on the layout.
6. Create a HTML field on the Cases module (e.g. invalid_quote_alert_c). The field should have the following properties:
   1. A message that indicates why the user will not be able to save the case. I usually make these alerts bold and in red.
   2. Dependent with the following formula:
      

      and(
          not(
              equal(
                  related(
                      $cases_quotes_1,
                      "name"
                  ),
                  ""
              )
          ),
          equal(
              related(
                  $cases_quotes_1,
                  "case_related_c"
              ),
              "Yes"
          ),
          equal(
              $quote_saved_c,
              0
          )
      )

   3. Read only
7. Add both fields to the case record view layout.

Once that is done, the case record will show whether it's a valid quote:

Or an invalid quote:

I hope this helps!

Chris

EDIT: I realized my initial solution would only work for the first save of the case with a quote. Going back to that case would have resulted in a failure in the validation check when trying to save another update. I added steps 3 & 4 and updated the case formulas to account for that oversight.

Thanks for the details.

Honestly, I would consider this problem from different aspects.

If the main concern is preventing this from happening in the UI, then I would work on the related field.

Users are probably clicking the related field and selecting from the list view. You can force that list view to avoid listing the quotes that have cases. Using filters should also affect typeahead queries.

As a preventive measure, you could throw an exception before saving if the fetched row already has that field. Since they are 1-1, you should be able to check that as a field rather than a relationship.

Tevfik Tümer
Sr. Developer Support Engineer