Getting rid of circular reference without dropping necessary fields

MTS95 — Thu, 23 Feb 2023 01:30:05 GMT

Hi there,

I posted a question about something similar recently, but I don't think I accurately captured the extent of the problem I'm having.

I have an app that pulls data from two different surveys, [WEBSurvey] and [SERVICESurvey]. [SERVICESurvey] has a field [Overall, how satisfied are you?] that contains values ranking satisfaction like so:

[Overall, how satisfied are you?]

Very Satisfied

Somewhat Satisfied

Neutral

Somewhat Dissatisfied

Very Dissatisfied

[WEBSurvey] has two fields, Question and Answer, that contain values ranking satisfaction like so:

Question	Answer
How would you rate the quality of this website?	Excellent
How would you rate the quality of this website?	Good
How would you rate the quality of this website?	Satisfactory
How would you rate the quality of this website?	Unsatisfactory
How would you rate the quality of this website?	Poor
Overall, I am satisfied with my experience	Strongly Agree
Overall, I am satisfied with my experience	Agree
Overall, I am satisfied with my experience	Neutral
Overall, I am satisfied with my experience	Disagree
Overall, I am satisfied with my experience	Strongly Agree

The table [WEBSurvey] is linked to [SERVICESurvey] via two other tables with similar fields, like so:

[WEBSurvey], [Collectors], and [Responses] all extract their data from a Microsoft SQL Server, while [SERVICESurvey] gets its data from a REST connection.

Because the answers in the Answers field are similar to the possible values in the [Overall, how satisfied are you?] field, my client has asked me to make another field, AllSatisfactionRatings, that contains the following values:

AllSatisfactionRatings

Very Satisfied

Somewhat Satisfied

Neutral

Somewhat Dissatisfied

Very Dissatisfied

[test]:
Load * Inline [
"Overall, how satisfied are you?",Answer,AllSatisfactionRatings
Very Satisfied,Strongly agree,5 Very Satisfied
Very Satisfied,Excellent,5 Very Satisfied
Somewhat Satisfied,Agree,4 Somewhat Satisfied
Somewhat Satisfied,Good,4 Somewhat Satisfied
Somewhat Satisfied,Satisfied,4 Somewhat Satisfied
Neutral,Neutral,3 Neutral
Somewhat Dissatisfied,Disagree,2 Somewhat Dissatisfied
Somewhat Dissatisfied,Unsatisfactory,2 Somewhat Dissatisfied
Very Dissatisfied,Strongly disagree,1 Very Dissatisfied
Very Dissatisfied,Poor,1 Very Dissatisfied
];

[WEBSatisfactionRatings]:
Load
Answer,
AllSatisfactionRatings
Resident [test];

[SERVICESatisfactionRatings]:
Load
"Overall, how satisfied are you?",
AllSatisfactionRatings
Resident [test]
;

Drop table [test];

However, when I do that, this is the result:

------------------------------------------------------------------------------------------------------

Finished with error(s) and/or warning(s)

Circular reference:

One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. Loop(s) will be cut by setting one or more tables as loosely coupled.

0 forced error(s)

0 synthetic key(s)

------------------------------------------------------------------------------------------------------

What can I do? My first thought is to sever the connection between AgencyID in [SERVICESurvey] and [Collectors] (since that is what links the tables regardless of what server they are coming from), but I am uncertain what unintended consequences that might have. Any insight would be greatly appreciated. Thanks in advance!

topic Getting rid of circular reference without dropping necessary fields in App Development

Getting rid of circular reference without dropping necessary fields