When I designed my application (years ago starting with A5 Ver 5) I created my tables and a master form I call Customer Manager where I can display all data linked to each customer, quickly seeing the data from each separate table linked to the customer through a Customer ID field. As I have grown the amount of info I want to see as a snapshot I have run into some problems with my set and form designs. Let me try to explain.
I have several tables all related to my Customers table and I am using a master form based on a set that links the tables together as follows:
The master form is for viewing all data for one particular customer. The CUSTOMERS table holds the customer name, address, etc.
The REQUESTS table holds records for each separate request by a customer such as a request for a quote or a service call. Each customer can have multiple requests. The REQUESTS_DTL table holds records that link each request to one or more units (stove, fireplace, furnace, etc.). Each request can have multiple units.
The TRANSACTIONS table holds records for each seperate Invoice or contract, along with payments made, etc. Each customer can have multiple transactions.
The UNITS table holds records for each separate unit owned by each customer (stove, fireplace, furnace, etc.). Each customer can have multiple units.
On my master form I have a sub-form with tabs and each tab displays info from each of the other tables. The Request tab displays a browse that lists all request records for this customer. The Units tab displays a list of all units owned by this customer. The Transaction tab displays a list of all transaction records for this customer. Each of these lists are an embedded browse that links to the CustNum field. As the CustNum changes the data changes to display only those records linked to the currently selected customer number.
This all works fine and has been working for several years. Recently I wanted to add some data to the snapshot view on the requests tab. This tab has a broswe based on the REQUESTS table. The REQUESTS table does not have data for the Units associated with this request. The data on the Units is held in the UNITS table and is linked to the REQUESTS table using another table named REQUESTS_DTL.
When I add or view a Request record I can add one or more Units to this request and I have a separate form that is based on a different set than my master customer form. On this request form I can view multiple linked Units that are associated with this request. Each request can have multiple units linked through the REQUESTS_DTL table. Again, this all works fine.
But now, what I wanted to do was add a seperate browse on my master customer form that will show me the units linked to each request as I clicked on each request record in the request browse. When I tried to build the browse I was unable to get any Units data to show up on the available fields list. On this tab of my sub-form I am displaying a browse based on the REQUESTS table. If I click on a line in the browse I want the data in another browse to show me only the associated Units records. But I don't have a direct link to the Units data from the REQUESTS table in my set. This set was set up to link everything back to the CUSTOMERS table.
I tried adding a direct link in the set diagram between the REQUESTS table, through the REQUESTS_DTL table, to the UNITS table. This worked to show me what I wanted to see on the Requests tab. But it broke my units browse on my Units tab. When I removed this added link to the Units table my units browse was fixed. I don't want to lose my ability to browse all Units linked to each customer. That's more important to me than being able to quickly see the linked units on the Request tab.
But I am wondering if there is another way to do what I wanted on the Request tab browse. This works outside of my master form when based on another set. So I could open another form to display what I want. But I was hoping to keep it clean and all inside my master customer form, on the same tab as the request browse.
Is there a way to do this? Do I have to use a programming feature instead of the standard menus from the A5 interface? Maybe I need to use some other control instead of a tabbed form?
In a nutshell, I think what I am trying to do is display data from a set structure that is not associated with my current form and current set, but still be viewable inside the current form. Does that make sense?
Thanks for any tips.
Sean
I have several tables all related to my Customers table and I am using a master form based on a set that links the tables together as follows:
Code:
CUSTOMERS |===REQUESTS |===REQUESTS_DTL |===TRANSACTIONS |===UNITS
The REQUESTS table holds records for each separate request by a customer such as a request for a quote or a service call. Each customer can have multiple requests. The REQUESTS_DTL table holds records that link each request to one or more units (stove, fireplace, furnace, etc.). Each request can have multiple units.
The TRANSACTIONS table holds records for each seperate Invoice or contract, along with payments made, etc. Each customer can have multiple transactions.
The UNITS table holds records for each separate unit owned by each customer (stove, fireplace, furnace, etc.). Each customer can have multiple units.
On my master form I have a sub-form with tabs and each tab displays info from each of the other tables. The Request tab displays a browse that lists all request records for this customer. The Units tab displays a list of all units owned by this customer. The Transaction tab displays a list of all transaction records for this customer. Each of these lists are an embedded browse that links to the CustNum field. As the CustNum changes the data changes to display only those records linked to the currently selected customer number.
This all works fine and has been working for several years. Recently I wanted to add some data to the snapshot view on the requests tab. This tab has a broswe based on the REQUESTS table. The REQUESTS table does not have data for the Units associated with this request. The data on the Units is held in the UNITS table and is linked to the REQUESTS table using another table named REQUESTS_DTL.
When I add or view a Request record I can add one or more Units to this request and I have a separate form that is based on a different set than my master customer form. On this request form I can view multiple linked Units that are associated with this request. Each request can have multiple units linked through the REQUESTS_DTL table. Again, this all works fine.
But now, what I wanted to do was add a seperate browse on my master customer form that will show me the units linked to each request as I clicked on each request record in the request browse. When I tried to build the browse I was unable to get any Units data to show up on the available fields list. On this tab of my sub-form I am displaying a browse based on the REQUESTS table. If I click on a line in the browse I want the data in another browse to show me only the associated Units records. But I don't have a direct link to the Units data from the REQUESTS table in my set. This set was set up to link everything back to the CUSTOMERS table.
I tried adding a direct link in the set diagram between the REQUESTS table, through the REQUESTS_DTL table, to the UNITS table. This worked to show me what I wanted to see on the Requests tab. But it broke my units browse on my Units tab. When I removed this added link to the Units table my units browse was fixed. I don't want to lose my ability to browse all Units linked to each customer. That's more important to me than being able to quickly see the linked units on the Request tab.
But I am wondering if there is another way to do what I wanted on the Request tab browse. This works outside of my master form when based on another set. So I could open another form to display what I want. But I was hoping to keep it clean and all inside my master customer form, on the same tab as the request browse.
Is there a way to do this? Do I have to use a programming feature instead of the standard menus from the A5 interface? Maybe I need to use some other control instead of a tabbed form?
In a nutshell, I think what I am trying to do is display data from a set structure that is not associated with my current form and current set, but still be viewable inside the current form. Does that make sense?
Thanks for any tips.
Sean
Comment