Why multiple files? That can work, but complicates the design of your solution. It can be simpler to keep all of these tables in a single file.
What relationships have you defined linking these tables?
The classic relationships for invoicing would look like this:
Invoice::__pkInvoiceID = Invoice Detail::_fkInvoiceID
Product::__pkProductID = Invoice Detail::_fkProductID
Where each record in Invoice Detail documents the purchase of a qty of products on a specific invoice. Typically, a portal to Invoice Detail is placed on the Invoice layout.
How you link in the other two tables will depend on the nature of the data in these two tables and how they need to link to a record in (I assume) Invoice_Detail.
Does a given product match to one and only one record in product_categories? Does a given product match to one and only one record in product_SubCategories?
What data from these two tables needs to appear on your layout?
Do you need to use this info to control the values in value lists? (Select a category to get a list of the sub categories for that category, select a sub category to get a list of products from that sub category....)
The ralationships you describe for invoicing are what I have
and Product >----Categories-----<SubCategorie
it will force to have those relationship Invoice Detail>----Categories2 and Invoice Detail>----SubCategories2
In Product I show just the names of Categories and SubCategories same for Invoice Detail
In the Invoice Detail I need to be able to select Categories and SubCategories for unspecified product that are not define in product not regular product (enter manually)
So far I have work in the Product layout and the Sub categorie ID is ok but not the display SubCategorie Name.
my relation schema
Categories2 and SubCategories2 are Tutorial: What are Table Occurrences? that refer to the same data source tables as Categories and SubCategories.
Categories2 should not be needed. You can add fields from Categories directly to your portal to Invoice Details and they should display the correct information.
An added occurrence of SubCategories will be needed so that you have a direct link from the Products Table occurence to exactly one record in SubCategories
Invoice Details>------Products>------SubCategories 2
Products::fkidCategorieSous = SubCategories 2::pkidCategorieSous
That will allow you to add fields from SubCategories 2 to the portal row.
Not sure to understand or there is a problem
Here is the structure, with links
In th layout when selecting SubCategorie in the menu the the name display is not updated, by showing the pkIDSubcategorie i see that the value don't match so the value show is the one from the current SubCategorie
i choose 12 in product and the name display is the one from 9 the first matching the selected Categorie
My mistake on saying that you don't need Categorie 2. I see the issue there. You can either add fields from Categorie 2 to your portal row or you can add a Produit 2 linked by the same match fields as Produit and then link Categorie 2 to it by the idCategorie fields. This second option eliminates teh need for using the fkCategorie field in Detail_Facture to match to a record.
You can add fiels from CateorieSous 2 to your portal row, but no value will show until you enter/select a matching value in fkidCategorieSous.
1st Thanks a part of the solution was make possible by your comments
I made a simplified version of my database, was thinking of uplosding (not possible)
If I have the product working the invoice is not working then fix the Invoice and the product layout is not working
Just the sub categories is creating the problem
In the Product layout the subCategorie if choosen from a list fromSubCategories for those matching Categories and use the SubCategorie Product to display values.
On the invoice I have CategorieInvoice and CategoriesSub Invoice, the list for the last doesn't adjust or is empty. I have try various combination with no success except when I put match to Categories for matching, but that got product to break
obviously I get something wrong!
the list for the last doesn't adjust or is empty.
And what list is that? A conditional value list?
If that's what you mean, I can see whay a conditional value list of sub categories would work for products or invoices but not both. The "starting from" table occurrence you'd specify and the table occurrence of sub categories that you'd specify as the source of the values will not work for the other. But you can define two conditional value lists with different "starting from" and value source table occurences specified and then one can be used on each layout.