Thank you for your post.
If you perform a "Find" on a portal, it will find all records in the CURRENT table that meets the find criteria in the portal. It will NOT narrow down the the information being displayed, because the current record links to those related records.
To narrow down the related records, you would need to switch to the related table, perform the find, and then you can have a portal back to the original table to display the original data. Does that make sense?
I got lost on the last part; "you can have a portal back to the original table to display the original data."
Does that mean there is no solution?
There is a solution.
In your layout for the related table, draw a portal and display information from your original table. Once you find the related records, the data in the original table will be displayed in the portal.
I am confused. Let me back up. I have Table "A" which contains a portal displaying fields from Table "B". I want to be able to perform a "Find" on the fields displayed in the portal.
Tell me if I am understanding your instructions; I am to create a portal on table "B" displaying the information from table "A"? So, essentially - I will be viewing the information in reverse.
Is that correct?
You have it correct.
Using your example, if you are in Table A and perform a Find in the portal (Table B), it will find all records in Table A that meet the criteria in Table B. That is, it will find all records in Table A that meets the criteria in Table B. It will not limit the number of Table B entries.
Therefore, you need to go into a layout with Table B, and create a portal into Table A. Do the Find in Table B, and then you can see the related records in Table A.
I understand your directions now. But that doesn't suit my use very well. Here is why. I have several related businesses. Table "B" is where I record expenses for all of the businesses. These are all real estate related businesses, so... the "expenses" table has a field for;
1. the property address
2. the type of expense (utility bills, repairs, etc) each type of expense is entered with an
associated number.. for instance utility bills are expense type #1, repairs are expense
type #15. and so on.
3. the amount of the expense
....and so on.
Not all of the expenses are directly related to a property address - vehicle expenses for instance.
So... Table "B" is a huge list of expenses that may or may not related back to a property address. There are 10's of thousands of records in table "B"
Table "A" is information specific to the properties. Purchase info, mortgage info, renter info, and so on. From table "A" I would like to view all the expenses related to each property. There are only a couple hundred records in Table "A".
Right now I have a relationship set up between the two tables - using the property address as that relationship link. So, as you know already, I have a portal set up in Table "A" that allows me to view ALL the expenses related to any one particular address. But, when I am looking at mortgage information for instance ... I only want to see mortgage related expenses. Or perhaps I want to only see the repair expenses for that property.
So, I am thinking.... is there a way to set up a relationship link that is a bit more complex. Specific example for my situation.....
example - Property: 123 Main street...
Table "B" will have
12 records recording all the mortgage payments for 123 Main St
2 records recording property taxes for 123 Main St
4 records recording water bills for 123 Main St
9 records recording repair bills for 123 Main St
Now... From Table "A" I want one layout with Mortgage information
There will be text fields for descriptions of the mortgage terms,
number fields for interest rates
date filed for duration information
So, from this layout I can view all this general information about the mortgage for
123 Main St ... but, I also want to be able to see the 12 mortgage payments that are
recorded in Table "B"
Also, from a different layout in Table "A" i would like to view only the repair
expenses for 123 Main St.
Let's say that the all mortgage payments in Table "B" are given a category number of "#20".
So anytime a mortgage payment is made... for any property... it is categorized as expense type "#20". And let's say that "repairs" are expense type "#15".
So, now.... from Table "A" I want one layout that shows me:
expense type #15 for 123 Main St
and I want another layout that shows me
expense type #20 for 123 Main St.
Can a relationship be created that would be defined as:
when the property address (in Table "A" & Table "B") is the same, and the expense type
(in Table "A" & Table "B") are the same... then the relationship is created.
Or what other way can I pull over specific expense information from Table B
Thank you for the clarification.
Yes, you can have a relationship built on more than just one field. Currently, you probably have a property field linking the two tables together. In addition, use a Type of Expense field. That way, you can enter "Mortgage Payments" into the Type of Expense field, and only those Mortgage Payments will be listed in the portal. Using your example, if you enter "Mortgage Payments" into the Type of Expense field for property 123 Main Street, then the 12 records from Table B will be displayed.
Now, after you design this relationship, click on the bottom left icon to add another table. Select table "B", and on the relationship graph, table "B 2" will be displayed. You can use a different set of fields to relate to this table.
I think you may want to set up separate fields for each of the different types of expenses, and not have them visible on any layout. For example, you could create a "Mortgage" calculation field that is equal to "Mortgage", and then link that field to the relationship for one of the occurrences of table B. Do this with the other types. Then, you can switch to a layout for Mortgage information and see only the Mortgage payments listed. Does that make sense?
Let me know if you need clarification for anything I covered.