Skip to main content
Solved

Joining BAccount with Addfess


Forum|alt.badge.img+1

We have a Call Sales Report where the address of the customer needs to be on the top showing Address information such as Address1, 2, City, State, PostalCode. I am joining the BAccount with the Address using DefAddressID from the BAccount and AddressID from the Address in the report. I have tried using an Inner, Left, and Right JOIN and keep coming up with “Open server settings” error when using the Address with the BAccount.

Error:

I know that the first image shows the left join, but I have tried the other ones with the same results. The ultimate goal is to just the the address data for each customer that has been visited or called.

Best answer by lauraj46

Hi ​@wmatthews1877 ,

The problem is with the Parent Alias.  Each alias needs to be unique.  Once an alias is assigned then the alias itself is used in the Parent Table column and the Alias column should be left blank.  I tried to make this change on your report, however the parent alias assigned on row 1 didn’t appear in the Parent Table dropdown on row 2.  To be honest, I have generally only used aliases on the right hand side of a join, so I’m not sure if this is a bug or not.

In any event, it really isn’t necessary to alias the BAccount table in this case.  The typical use case for an alias is for the right hand side of the join when a table is joined more than once to retrieve different records.  For example if you need to join the BAccount for the CRActivity and a different BAccount that corresponds to the branch for the transaction.  In your example it is the same BAccount for both the activity and the address, so no need for an alias.

I would recommend removing alias from both joins, then clicking ‘Apply’ which should update the formulas on your report.  I made this change it worked without errors.

 

Hope this helps!

Laura

View original
Did this topic help you find an answer to your question?

12 replies

lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 568 replies
  • December 12, 2024

Hi ​@wmatthews1877 ,

Your join looks fine, it looks like the report definition itself is corrupt.  I would recommend reverting to an earlier version or starting over, and trying again.

Hope this helps!

Laura


Forum|alt.badge.img+1
  • Author
  • Varsity I
  • 130 replies
  • December 12, 2024

Thank you. Just as a matter of curiosity, how can you tell if the report definition is corrupt? Going forward, I would like to be able to troubleshoot from that aspect so that I can expand my knowledge. The report I am currently using is from our “Pilot” (our sandbox), so I took the production copy and copied that over to the sandbox.


lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 568 replies
  • December 12, 2024

Hi ​@wmatthews1877 ,

The report file itself is an XML text file with the extension .rpx.  You could probably validate the xml, but normally the way to tell is an error message like the one you saw :).

Laura


Forum|alt.badge.img+1
  • Author
  • Varsity I
  • 130 replies
  • December 12, 2024

That makes sense. Using the production report in the sandbox, with the same joins, gives me the same error message. I may have to start from a blank slate. This is strange as the report is in production, and all I want to do is add the complete address for more informational purposes.


Forum|alt.badge.img+1
  • Author
  • Varsity I
  • 130 replies
  • December 12, 2024

If you happen to think of anything else I can try, please respond back as I appreciate any help the Community has to offer.


Forum|alt.badge.img+1
  • Author
  • Varsity I
  • 130 replies
  • December 16, 2024

I made a brand new call sales report from scratch. As soon as I use the BAccount with the Address table I get the error message “Open server settings”, and that is whether I use a LEFT, RIGHT, or INNER JOIN. The JOIN is BAccount→ DefAddressID, and Address→ AddressID. I’ve even tried using the BAccountID on both of them alone and in conjunction with the DefAddressID, AddressID.


Forum|alt.badge.img+1
  • Author
  • Varsity I
  • 130 replies
  • December 16, 2024

Using just BAccount with no other fields. I am getting the following issue. How can it be BAccount be invalid when it is plainly a table in the DAC?

 


lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 568 replies
  • December 16, 2024

Hi ​@wmatthews1877 ,

Interesting.  Can you please post your report and I will take a look?

Laura 


Forum|alt.badge.img+1
  • Author
  • Varsity I
  • 130 replies
  • December 16, 2024

I appreciate any help you can furnish with this. I’ve tried using the CRAddress and the Address. I think that maybe there may be something within the Filters Collection Editor though I have not been able to pinpoint it. I also have an issue with the originator of the report using the CRActivity.StartDate as the @StartDate and @EndDate in the Filters Collection Editor, but that can be addressed once the other issues are fixed. My thinking is the CRActivity does have an EndDate, why not use it?


Forum|alt.badge.img+1
  • Author
  • Varsity I
  • 130 replies
  • December 16, 2024

I was able to get one similar to the original, but it’s not all on one continuous sheet. I put the same coding in the original as I did to the copy and it still gives me the error even after comparing the two. I am attaching the one I just made, and maybe another set of eyes can see what I can’t. The obvious question is why don’t I use the one I just made. There are subtle differences between the two and I’d like to try and stay with the original if I can.


lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 568 replies
  • Answer
  • December 16, 2024

Hi ​@wmatthews1877 ,

The problem is with the Parent Alias.  Each alias needs to be unique.  Once an alias is assigned then the alias itself is used in the Parent Table column and the Alias column should be left blank.  I tried to make this change on your report, however the parent alias assigned on row 1 didn’t appear in the Parent Table dropdown on row 2.  To be honest, I have generally only used aliases on the right hand side of a join, so I’m not sure if this is a bug or not.

In any event, it really isn’t necessary to alias the BAccount table in this case.  The typical use case for an alias is for the right hand side of the join when a table is joined more than once to retrieve different records.  For example if you need to join the BAccount for the CRActivity and a different BAccount that corresponds to the branch for the transaction.  In your example it is the same BAccount for both the activity and the address, so no need for an alias.

I would recommend removing alias from both joins, then clicking ‘Apply’ which should update the formulas on your report.  I made this change it worked without errors.

 

Hope this helps!

Laura


Forum|alt.badge.img+1
  • Author
  • Varsity I
  • 130 replies
  • December 16, 2024

That worked like a charm, and thank you.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings