8 Replies Latest reply on Nov 5, 2013 10:00 AM by AnthonyTesoriero

Calculation based on value list from related record

Title

Calculation based on value list from related record

Post

I have about 8 hours invested in attempting to figure out what should be a basic calculation but I'm now at the "throw something through the screen" level and asking for help...

I have 2 date fields called "date of notice to owner" and "date of notice to lien-holders".

The date of both will always match however while there is always an owner there is not always a lien holder therefore I need the field to populate based upon a calculation.

All lien holder and owner records are contained in a related database and defined by a "type" field to distinguish them.

"Date of notice to owner" automatically fills in based on a different calculation call "auction date"

I need the proper formula to get "date of notice to lien-holders" to fill in automatically or remain blank based on whether or not a record does or does not have a related lien holder record.

I've tried "If" "Case" "is Empty" "Evaluate" logic expressions based on text and numbers. I cannot get the filed to work in a manner that it auto populates and or erases if lienholder have been deleted.

• 1. Re: Calculation based on value list from related record

And what is the relationship between the table where you are defining this calculation and your table of owners and lien holders?

By adding a new Tutorial: What are Table Occurrences? referring to that table, it should be possible to set up a relationship that matches only to lien holders. Then you can set up a calculation that uses that relationship in order to check for the existence of any lien holders for a given record in your main table.

• 2. Re: Calculation based on value list from related record

PhilModJunk,

Thank you for your reply. The relationship where I'm defining the calculation is the main database called "Lien". For our purposes, A "client" can have many "Liens" and a Lien can have many "Customers/Lienholders" but a "Lien" can belong to only one "Client"...

On your other note regarding table occurrences, it sounds like a logical idea so I followed your link to your explanation and followed the instructions in what I thought was an exact manner however the field "Phone Number" from the 2nd table occurance is showing nothing. I tried it as both a regular field as well as a portal field and it remains blank, so I am now down another rabbit hole wondering what I did wrong and still baffled how to fix my original problem. I attached screen shots, I hope you can help me make heads of tails of this.. Thanks in advance

• 3. Re: Calculation based on value list from related record
/files/04cb9eac54/Untitled_2.png 719x535
• 4. Re: Calculation based on value list from related record
/files/62cfa0e391/Untitled_3.png 654x438
• 5. Re: Calculation based on value list from related record

Your relationships/tables don't appear to match what you describe in words.

A "client" can have many "Liens" and a Lien can have many "Customers/Lienholders" but a "Lien" can belong to only one "Client"...

Don't see what that has to do with a relationship between a contact record and the phone numbers that belong to that contact. It would seem that your relationships should look like this:

Client------<Liens-----<LienHolders

Client::__pkContactID = Liens::_fkClientID
Liens::__pkLienID = LienHolders::_fkLienID

Client and Lienholders could be table occurrences of the same data source table.

And if a lienholder can have more than one lien, a join table between liens and lienholders is needed.

• 6. Re: Calculation based on value list from related record

PhilModJunk,

Sorry if I confused the issue with the Screen shots and mixing related issues.  The screen shots are a reference to my attempts to duplicate your instructions on your previous post regarding  Tutorial: What are Table Occurrences?

I read your original comment and felt it was a good idea so I followed your link to  Tutorial: What are Table Occurrences? and followed your instructions exactly to try an practice before attempting to implement the idea into our "lien" database. Unfortunately I fell short and could not get your example to work for me. The screen shots where to show you my work to see if you could pick up where I went wrong. The screen shots are NOT part of my lien database.

• 7. Re: Calculation based on value list from related record

Make sure that cFax has "text" specified as the result type. PhoneType should also be of type text.

• 8. Re: Calculation based on value list from related record

Thank you. That solved it. I will now try to use that understanding to solve the original question.