By using this website, you agree to our Terms of Use (click here)
Hi All,
I'm putting together a schema diagram in Access to help me understand all of the relationships as I dig into Acumatica and making GIs and reports.
Access is the first thing that came to mind. Is there a better tool to diagram table schema?
Would anyone else like to collaborate?
-Matt
Ç
Hey Matt,
Great idea. This is definitely a hot topic and currently the most popularly requested item on the Acumatica Feedback website:
https://feedback.acumatica.com/ideas/ACU-I-639
But the problem is not what tool to use. The problem is that creating a schema would be an "outside" diagram that would have to be manually maintained with every new build of Acumatica. This isn't a problem with legacy ERP software like Sage because the product barely changes over the years. But, with Acumatica, it's a problem because the product continues to evolve at a steady pace.
I have had multiple conversations with Acumatica engineers about this. And they have tried to do it. I've seen evidence of a lot of time spent on it. One solution looked really cool, but it took hours and hours to generate every time which was too expensive from a resource standpoint. But they have a lot of smart people there. Just keep adding those votes to the feedback item listed above and I think they'll eventually find a way to auto generate the schema with each new build.
In the meantime, I'd love to see what you come up with. It's just not something that I personally want to invest a lot of time in at the moment.
Added my vote to that idea. I've been doing this the hard way when building new GIs. I use existing reports and GIs to determine the relationships for my new GIs.
Personally, I look at the Primary Key fields on the Tables in the Database to know what fields to use in the joins. That way I know that I won't get duplicate records.
You would need access to the database, but anyone can download and install Acumatica.
First, you would install SQL Server Express Edition (free).
Second, you would install a version of Acumatica (free, only limited to 2 concurrent users):
http://builds.acumatica.com
Then you can browse the database using SQL Server Express Edition.
@matthew-hoss.
Were you able to get anywhere with this?
I just got thrown into acumatica and feel a little lost. If you are willing to share what you were able to put together I would be very appreciative. I am also very willing to work on this more with you if there is still an interest.
Thanks,
Jon
Have you managed to find anything that was helpful to you ?
I am in the same situation as you and am desperate for some help
I just noticed this new thing in Acumatica 2019 R2!
It's still missing Primary Keys, but it's a start.
You don't actually need a 2019 R2 environment because the help is available outside of Acumatica:
https://help-2019r2.acumatica.com
I still find myself using a local copy of Microsoft SQL Management Studio to check for primary keys on the database tables. Technically, this isn't the same thing as finding primary keys for a Data Access Class, but it works for me.
sp_pkeys is a quick way to find the keys for a table, like this:
