Help with Dilemma! Please Read!!

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
Help me solve this problem!

Ok so here is the scenario.

I have a table of accounts.  Several of these accounts can be related on that same table by a common ID.

EX: Account ABC Part 1 – ID 234
       Account ABC Part 2 – ID 234
       Account ABC Part 3 – ID 234


So the actual name of the account will be different, but could have the same ID.

These various accounts will have a status:
EX: Account ABC Part 1 – ID 234 - Closed
       Account ABC Part 2 – ID 234 - Closed
       Account ABC Part 3 – ID 234 – Open

My dilemma is this.  I need to know for all that have the same ID if any one of them is Open, then it is a current Account.  If all of the accounts with the same ID are Closed, then it is a former account.

I need a way to “tag” all the Accounts with whether or not they are ALL closed or if any one of them is still open.

I know I may need a separate table that uses the one common ID for the accounts – I am just not sure how to set it up to get what I need.  **A note, I need to “tag” if they are current or former on the Accounts table they are currently in.
Photo of Stephanie

Stephanie

  • 10 Points

Posted 3 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 800 Points 500 badge 2x thumb
One way to do this:

1)  Create a new table - called "IDs"

As you were thinking, each record in this "IDs" table would hold a unique ID.


e.g. ID = 234, ID = 123, etc.

You can create a Text (or Number) field called ID.  This field should be Unique.

2) Create a Relationship to the Accounts table:  1 ID has many Accounts

3) Assign each Account to the corresponding ID parent record

So for each Account with ID = 234, the Related ID should be the record with ID 234


EX: Account ABC Part 1 – ID 234
       Account ABC Part 2 – ID 234
       Account ABC Part 3 – ID 234


These should all be "children" of the ID = 234 "parent" record.

4) Create summary field [# of Open Accounts]

In the Relationship - create a new summary field - Number of records where [Status] is "Open".

5)  Create formula field in ID [ID Status]

Create a Formula Checkbox field in the ID table - called [Current Account]

The formula would be:  If ([# of Open Accounts] > 0, true, false)


So if there is at least one Open Account, then [Current Account] = true.  Otherwise, [Current Account] = false.

If you want, you could also do something similar with a Formula Text field instead - to display "Open" or "closed".

6)  Create a lookup field [ID - Current Account]

In the Relationship, create a lookup field from the Accounts table - to look up the [Current Account] field in the IDs table.  This would be called [ID - Current Account].  You can rename it as you want.


This means that each Account would now know whether all the Accounts with the same ID are open or closed - based on whether [Current Account] is checked.



Depending on what you need, you can tweak this structure - use text fields instead of checkboxes, adjust the formulas, etc.  But the overall setup should get you what you're looking for.
Photo of Stephanie

Stephanie

  • 10 Points
I'm sorry - I didn't let you know before, but your answer was a LIFE SAVER!  I cannot express how grateful I am !! Thank you so very much for sharing your genius :)
Photo of Xavier Fan

Xavier Fan, Champion

  • 800 Points 500 badge 2x thumb
You're welcome - glad it helped!