Skip to main content
Solved

Joining BAccount with Addfess

W
Varsity I
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
William J Matthews
    Did this topic help you find an answer to your question?

    12 replies

    lauraj46
    Captain II
    Forum|alt.badge.img+8
    • lauraj46
    • 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

    Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
    Chris Hackett
    1 person likes this
    • Chris Hackett
      Chris Hackett
    W
    Varsity I
    Forum|alt.badge.img+1
    • wmatthews1877
    • 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.

    William J Matthews
    lauraj46
    Captain II
    Forum|alt.badge.img+8
    • lauraj46
    • 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

    Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
    Chris Hackett
    1 person likes this
    • Chris Hackett
      Chris Hackett
    W
    Varsity I
    Forum|alt.badge.img+1
    • wmatthews1877
    • 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.

    William J Matthews
    W
    Varsity I
    Forum|alt.badge.img+1
    • wmatthews1877
    • 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.

    William J Matthews
    W
    Varsity I
    Forum|alt.badge.img+1
    • wmatthews1877
    • 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.

    William J Matthews
    W
    Varsity I
    Forum|alt.badge.img+1
    • wmatthews1877
    • 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?

     

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

    Hi ​@wmatthews1877 ,

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

    Laura 

    Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
    W
    Varsity I
    Forum|alt.badge.img+1
    • wmatthews1877
    • 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?

    1 Attachments
    William J Matthews
    W
    Varsity I
    Forum|alt.badge.img+1
    • wmatthews1877
    • 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.

    1 Attachments
    William J Matthews
    lauraj46
    Captain II
    Forum|alt.badge.img+8
    • lauraj46
    • 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

    Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
    Chris Hackett
    WillH
    2 people like this
    • Chris Hackett
      Chris Hackett
    • WillH
      WillH
    W
    Varsity I
    Forum|alt.badge.img+1
    • wmatthews1877
    • Author
    • Varsity I
    • 130 replies
    • December 16, 2024

    That worked like a charm, and thank you.

    William J Matthews
    Chris Hackett
    1 person likes this
    • Chris Hackett
      Chris Hackett

    Reply


    Most helpful members this week

    Terms & ConditionsCookie settings

    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
    About Acumatica ERP system
    Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
    © 2008 — 2024  Acumatica, Inc. All rights reserved