Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Refining an Ambiguous Relationship for Portal Calculation


gregspiral
 Share

Recommended Posts

I am finishing a database for a chiropractor that allows for clients to be charged or for insurers to be charged. I have a portal on a tab of the main client layout that should show the totals for each payor - pictured below. (Right Click to open the images in a new tab & you can see the whole thing in decent resolution):

 

ClientPortal.png

 

The client layout is based on the client table, and the portal is based on the payor table matched to invoice. The problem is that rather than showing the balance for charges associated with only the current client, it shows all the charges for that insurer. So rather than showing totals for just Fred Smith's ACME charges, it is showing totals for all ACME charges for everyone.

 

I think I understand why. When I look at the relationships I can see that the payor relationship is a little ambiguous.

ClientID is matched to Service table

ServiceID is matched to Invoice table

InvoiceID is matche to a Payor table - where the calculation takes place

-pictured below:

 

Relationships.png

 

Ideally I would like to keep this portal and either filter its results or create some new fields/calculations to place in the portal instead of my current fields. So far, I've had no luck. I tried to create a filter; but I think my logic was circuitous. I thought about putting simple calculations in the invoice field using if statements to identify each payor. But then I'd have to make +100 of fields to capture all different payors.

 

How can I refine these results so they are just related to the client?

 

Thanks for any help.

Link to comment
Share on other sites

I tried many things; but just couldn't find a way to make this work. So I settled for scripting a less elegant solution--button that opens new layout, finds invoices only for the client, summarizes those by payor, and hides the body sections so only totals display. It works, it's fine; but I'd still love better ideas if anyone has one.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use