Modify SubForm Data

[dschwartzer]

I am fairly new to LibreOffice in general and Base in particular and need some help with my form. Here is some background.

I have created a database with multiple tables including a Company table, a Product table, a ProductCategory table and a Media Contact table. The Company table has a 1:n relationship with the Product table (one Company can have many products) and 1:n with the MediaContact table (a Company can have many Media Contacts). The ProductCategory table has a 1:n relationship with the Product table (A Product Category can be associated with many products but a product can have only one category).

I have created a form for the Company table and have included a subform (table grid format) to show the many products associated with that company. In the subform, the ProductCategory is displayed as the FK reference to the ProductCategory PK.

I know that on the main form, if I have a FK showing in a data element, I can add a control to show the name associated with the FK (i.e. show (name) John Smith instead of (NameID) “5”). I would like to be able to do that same thing in the subform so the Product subform shows Product Category (Camera) instead of ProductCategoryID (3).

I would like to do the same thing with the Media Contact that is a FK on the Product table.

Can that be done?

Thanks for your help.

Regards,

David

[Ratslinger]

Providing a specific answer to your question is difficult at best given the provided information. Most challenging is approach and what is trying to be accomplished overall. How data is displayed is not reliant upon keys specifically.

For one example, and saying space is not necessarily an issue, in your Product Category table, it appears these are unique names. If so, use the name as the key (and only field in table) and use a List Box for selection. This can be seen in the attached sample on both of the forms.

There is a sample attached with two forms and tables based as best upon what you provided. Form “COMPANY” has three sections. Top is from Company table info & navigation bar, middle is Product table (sub-form) and bottom is Media Contact (sub-sub-form). When a company is selected, all products for that company are listed in the table control. Whichever product item is selected will display the associated Media Contact information. Media Contact names cannot be shown (as was done for Product Category) since you are still entering data for the record and selection can only be from stored information.

Form “COMPANY1” uses a Filter for access. Filters can be used for many purposes. It is a single record used for nothing more than temporary storage - in this case the Company ID. The form itself is nearly identical to the first with a listbox and button at the top. This listbox gets all the items from the Company table and stores the selected item in the Filter record COMPANY field. This field is then used as a filtering mechanism in the subsequent subforms to get specific records. When an item has been selected in the listbox, the information needs to be displayed based upon the selection. The button accomplishes this by refreshing the subforms. The refresh causes a selection based upon the new information in the Filter field. Contrary to the first form, now the Media names are available because we have a specific Company ID. The Media data is still based upon MEDIACONTACTID but you can now see the names available for only that company in the dropdown in the mName field in the PRODUCT table.

Believe me, this isn’t intended to confuse you, but rather to show there are many, many ways to go about accomplishing things in Base - and this doesn’t even touch on coding your own macros (a fairly large learning curve). You need to take some time in evaluating the different controls and their capabilities to determine what is best suited for your project.

In direct answer to your question, yes it can be done but probably not with the approach it seems you have taken.

Sample: KeySubstitute.odb

[dschwartzer]

Thank you for this information. I didn’t realize you had posted it so sorry for the delay. This looks great but I think I need to spend some time to try and understand it (this is all very new to me.

I’ll circle back if I need more help.

Regards,

David

[dschwartzer]

Thought I would take a step back. Attached are my tables and relationships. I have tried to change the PK between Product and ProductCategory tables but keep getting an error message. As a first step, do you think these tables and relationships make sense?

/Users/david/Desktop/Screen Shot 2017-01-07 at 1.13.04 PM.jpg

[Ratslinger]

@dschwartzer there is nothing to look at. You cannot put an image in a comment, only in questions & answers.