value list from multiple tables

[madman411]

Hi all

I'm exploring the option of adding this feature if it's feasible or if another solution is warranted.

Currently I'm working on a property management database to manage rental properties I have. In the Invoice table/portal I use to track charges and rent payments for each lease I have a Tenant field that allows me to tag a particular tenant within the lease to that charge or payment (useful if the lease has more than one tenant and they all pay separately, also to track how much each tenant pays individually). This is done using a pop-up menu and using the value from a second field that shows their name rather than their ID number from the first field. Currently this works a treat.

I'm finding I need to additionally track/tag payments made/received by vendors, such as fees deducted by other companies I have managing some properties. I'd like this value list to additionally include vendors for that property. The value list is filtered currently to only show tenants in that lease and I would like to maintain that.

I have created a new table to populate this VL (LeaseVendorVL)  but I have confused myself on how to structure the relationships and record(s) I need to create within it.

Any ideas or suggestions?

[comment]

Consider placing both tenants and vendors in a single Contacts table.

 

[madman411]

1 hour ago, comment said:

Consider placing both tenants and vendors in a single Contacts table.

 

Hi comment. I thought of that. But was curious how I can leave the value list filtered for the related tenants but have all the vendors appear in the same list. So, filtered tenants but all vendors. 

[comment]

It depends on how the relevant tables are related. Here is one option you could explore: if you have a value list of related tenants, and a value list of all vendors, then you can define an (unstored) calculation field combining the two lists and use this as the match field to another occurrence of the Contacts table. Then define the final value list based on this relationship. But that's just one of many possible alternatives. Perhaps you could keep your current structure and use another field to record the payee/payer ID when it's a vendor.

 

Edited July 28, 2021 by comment

[madman411]

comment - going to explore your suggestion and see if I can get it to work.

I know if I script or enter a carriage return inside the ID match field with the LeaseID in the Vendor record I can get the "vendor" record(s) to show in any lease and still include the related tenant records inside the value list.

When you say 'define an (unstored) calculation field combining the two lists' I assume you're referring to using the List() function to do so?

I also wanted to add a '-' divider to separate the tenants and "vendors" in the list, but I know that's opening another can of worms.

Thanks for your help!

[comment]

1 hour ago, madman411 said:

When you say 'define an (unstored) calculation field combining the two lists' I assume you're referring to using the List() function to do so?

Either that or  List A & ¶ & List B.

 

1 hour ago, madman411 said:

I also wanted to add a '-' divider to separate the tenants and "vendors" i

You might want to move from a pop-up menu to selecting from a card window showing a list view of the Contacts table. Then you can find the exact set of records you want to choose from, as well as sort them and use a sub-summary part to separate between groups. And you won't have to add anything to your relationships graph for this.