Value list with calc or condition?

[Hawaii80]

I have a value list pulling tenant last names from the tenant table, easy.

 

I also have a calc field on the tenants table to display if a tenant is active like so, If ( Tenants::Lease End

 

Now, I have another table called tenant payments, related to the tenants table and on tenant payments have a drop down value list displaying all tenants last name so I can choose proper tenant when applying payment. Problem though is I'm trying to figure out how in this value list I can show only those "Active" tenants not both active and inactive; otherwise it will be a never ending list. I tried a calc both as a conditional formula, (didn't work) and in the tables field field but can't seem to get the list to display only active tenants. Any ideas?

[Weetbicks]

Create a new calculation on your tenant payments table called "constant Active", simply set its contents to "Active".

 

Now, create a relationship to a tenants table occurrence, lets call the new table occurrence "Active Tenants Last Names"

 

Tenant Payments -> Active Tenants Last Names

-------------------------------------------------------

Constant Active = Tenant Status

 

 

The purpose of the relationship is, that it will produce a list of all Active tenants only (for this to work, your tenant status field will have to be indexed (set indexing option to none and tick "index if required" or whatever it is called, I forget..)

 

Now modify your value list of tenant last names to use this relationship. So you'll set it up to perhaps return Tenant IDs from the "Active Tenants Last Names" occurrence, and you would choose the second field as "Last Name", then choose the option "show all related values starting from" and choose "tenant payments". Choose to show only 2nd field, and sort however you want.

 

If you are on the tenant payments layout, the value list will show all values thru the relatioship (ie all active tenants).

[David Head]

Now modify your value list of tenant last names to use this relationship. So you'll set it up to perhaps return Tenant IDs from the "Active Tenants Last Names" occurrence, and you would choose the second field as "Last Name", then choose the option "show all related values starting from" and choose "tenant payments". Choose to show only 2nd field, and sort however you want.

But be aware that this method will only show the first active tenant for each last name - if you have 40 active Smiths, only one will be in the list. smiley-wink

 

Your second field needs to be a calculated unique value in order to get all records showing when sorting by the second field.

[Weetbicks]

Good point, like maybe it should be

 

Last Name & ", " & First Name

 

err...providing no two people have the same name..

[Hawaii80]

But be aware that this method will only show the first active tenant for each last name - if you have 40 active Smiths, only one will be in the list. smiley-wink

 

Your second field needs to be a calculated unique value in order to get all records showing when sorting by the second field.

 

Good point, what I did for now was use the apartment number of the property, which does show the different last names tied to a specific unit, (like 23G Smith, 8B Smith) property. The only time I can see that being an issue is having the same name for the same apartment then I may have to tie the lease dates to it instead, or come up with a different method.

 

Thanks for the input.

[Hawaii80]

Create a new calculation on your tenant payments table called "constant Active", simply set its contents to "Active".

 

Now, create a relationship to a tenants table occurrence, lets call the new table occurrence "Active Tenants Last Names"

 

Tenant Payments -> Active Tenants Last Names

-------------------------------------------------------

Constant Active = Tenant Status

 

 

The purpose of the relationship is, that it will produce a list of all Active tenants only (for this to work, your tenant status field will have to be indexed (set indexing option to none and tick "index if required" or whatever it is called, I forget..)

 

Now modify your value list of tenant last names to use this relationship. So you'll set it up to perhaps return Tenant IDs from the "Active Tenants Last Names" occurrence, and you would choose the second field as "Last Name", then choose the option "show all related values starting from" and choose "tenant payments". Choose to show only 2nd field, and sort however you want.

 

If you are on the tenant payments layout, the value list will show all values thru the relatioship (ie all active tenants).

 

Thanks Weetbix, that did the trick.

 

Only issue now is the rental address not showing up. What I had was the rental address field from the properties table on the tenants payment table before and had a relationship between property table apartment # to tenants apartment # and it worked fine. But, I now am only utilizing the active tenant value list and the address field is not populating. I did try to add a relationship from active tenants table to properties but that didn't seem to work. Not a biggie, I don't have to have the rental address on the payments table, just wondered why the relationship wouldn't work.

[Hawaii80]

Okay, here's an issue that I'm now dealing with...

 

Everything works great with the value list showing only active tenants. I had to make the value list though show the apartment for first field and second field tenants last name; otherwise accounting has no way of knowing which apartment they are tied to when entering a tenant payment.

 

The problem that arises with this is on the ledger, which is a portal on the tenants table. It is showing debits and credits for any tenant that stayed in the same apartment vs. the specific tenant in tenant payments that the credit/debit was assigned to. I realize that it's doing it, (I assume) because I'm tying the apartment and tenant in the value list, but I can't find another way to tie them together to also allow the user to know which tenant it is. I can't just do first name and last name only because I have for example companies that may rent 3 or more apartments at a time and if I don't show both apartment number and tenant last name accounting will only see their name 3 times but not know which one is for which unit when entering a payment.

 

Any suggestions on how best to tackle this? Thanks again.

[Hawaii80]

David or Weetbix I do have a questions for the value list. Everything is working great but I noticed in "Find Mode" the drop down shows no values defined so I can't search by the value list. Browse mode everything is in there, is there something that I'm missing that I don't have the ability to search in the value list when in Find Mode?

[David Head]

... I noticed in "Find Mode" the drop down shows no values defined so I can't search by the value list.

Think about how your conditional value list works - it displays all tenant records that match the value of the constant (calculation).

 

Question: Does that calculation evaluate in Find mode?

Answer: No, not for a standard calculation.

 

Solution: Set the calculation to global storage.

 

QED (?)

[Hawaii80]

Think about how your conditional value list works - it displays all tenant records that match the value of the constant (calculation).

 

Question: Does that calculation evaluate in Find mode?

Answer: No, not for a standard calculation.

 

Solution: Set the calculation to global storage.

 

QED (?)

 

I understand, makes sense now. Thanks for the explanation David.

[poetnabotl]

Tenant Payments -> Active Tenants Last Names

-------------------------------------------------------

Constant Active = Tenant Status

 

 

The purpose of the relationship is, that it will produce a list of all Active tenants only (for this to work, your tenant status field will have to be indexed (set indexing option to none and tick "index if required" or whatever it is called, I forget..)

 

I have a similar set up going on, I think. And I think my results aren't showing up in my value list because my "active/inactive" field is the result of a calculation and is set to "do not store calculation results". Any way around that?

[Weetbicks]

Turn storage on for the calc if you can. If the fields in the calc that determine the status come from the same table, you should be able to store them.

[poetnabotl]

Unfortunately, it's based on Get(CurrentDate). Is there a way, perhaps, to create another, indexed field that updates as my calculation field updates?

[poetnabotl]

Any suggestions, anyone? classes::classStatus is a calculation based on Get(CurrentDate) and classes::classEndDate (results is text, "active" or "over"). On a different table (students), a value list showing classes::classID, but I'd like to show only classIDs that are listed as "active" in the classes::classStatus field. Given that classes::classStatus is unstored, suggestions on how to use that field to filter a value list? Or should I make classes::classStatus stored and then somehow use a script to update it when the file opens? If so, erm, can anyone get me on the right path?

Thanks as always.

Jason.