Announcement

Collapse
No announcement yet.

looking to build a report to search out parent/child account relationships...

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • looking to build a report to search out parent/child account relationships...

    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.

  • #2
    account_id table, there are the rows `parrent_account`, `parent_account_id`, and `school_district_parent_organization`
    This is a custom table with custom fields and relationships.

    When you create a simple list report for the Account entity type with the Parent-Account runtime filter, it should list child accounts. I don't know why it didn't work for you.

    Comment


    • #3
      Originally posted by yuri View Post

      When you create a simple list report for the Account entity type with the Parent-Account runtime filter, it should list child accounts. I don't know why it didn't work for you.
      I saw that, and I can get the report to list accounts with parents, but that's not quite what I want. I want a report that lists the children of a given account.

      [EDIT] I think I can solve the problem myself if I can get access to the queries used in the reports... is that possible?
      Last edited by rudepeople; 01-09-2023, 02:20 PM. Reason: adding question

      Comment

      Working...
      X