Tweet
I'm looking into buildint a mysql report that seeks out parent account links. The purpose is to locate all the children of any given parent account. I am seeing several places in the database to link to, but everything I find that seems like a link is basically empty!
For example; in the account_id table, there are the rows `parrent_account`, `parent_account_id`, and `school_district_parent_organization` (That last one kind of gives away the game here... our clients are schools and school districts) I'm not sure where the last row came from. I did not create it and I'm not entirely sure how espo uses it as it doesn't appear anywhere in the account within the website. Anyway, all of the above rows are either empty or "[null]" with a handful of exceptions that don't seem to line up with what I see in the website (the `school_district_parent_organization` field has a few with district names that seem to coincide with some districts, but most are empty. In addition to the above, there is a table named `parent_account` but said table is completely empty.
I ran these query to try to find any other place where accounts could be linked:
SELECT table_name, column_name from information_schema.columns where column_name LIKE '%parent%';
SELECT table_name, column_name from information_schema.columns where column_name LIKE '%account_id%';
I found the results somewhat enlightening, but I still cant see any kind of link between accounts.
Before I go too much further down this rabbit hole, I think I need to ask here; how are child accounts linked to their parent accounts? is there a simple way (within the espo website) to glean what child accounts a parent account has?
I checked the report section and there is the ability to generate a report of what accounts have a parent, but that produces too much data to be useful. I don't see a way to list out child accounts of a specific parent account (maybe I'm doing it wrong?). I'm already set to build an external report to grab the info, but I need to know what to query. If there's already something internal to the website (like a way to tease out the data I want in the internal reports generator), so much the better! But again, I didn't see anything useful.
For example; in the account_id table, there are the rows `parrent_account`, `parent_account_id`, and `school_district_parent_organization` (That last one kind of gives away the game here... our clients are schools and school districts) I'm not sure where the last row came from. I did not create it and I'm not entirely sure how espo uses it as it doesn't appear anywhere in the account within the website. Anyway, all of the above rows are either empty or "[null]" with a handful of exceptions that don't seem to line up with what I see in the website (the `school_district_parent_organization` field has a few with district names that seem to coincide with some districts, but most are empty. In addition to the above, there is a table named `parent_account` but said table is completely empty.
I ran these query to try to find any other place where accounts could be linked:
SELECT table_name, column_name from information_schema.columns where column_name LIKE '%parent%';
SELECT table_name, column_name from information_schema.columns where column_name LIKE '%account_id%';
I found the results somewhat enlightening, but I still cant see any kind of link between accounts.
Before I go too much further down this rabbit hole, I think I need to ask here; how are child accounts linked to their parent accounts? is there a simple way (within the espo website) to glean what child accounts a parent account has?
I checked the report section and there is the ability to generate a report of what accounts have a parent, but that produces too much data to be useful. I don't see a way to list out child accounts of a specific parent account (maybe I'm doing it wrong?). I'm already set to build an external report to grab the info, but I need to know what to query. If there's already something internal to the website (like a way to tease out the data I want in the internal reports generator), so much the better! But again, I didn't see anything useful.
Comment