Using table control for choosing in many-to-many relation

English
#1

I have a many-to-many relation in a simple base that provide stock capabilities.

First table present a stock cells that have a rack, row, col.

Second table present a cargo.

Third table provide many to many relation and quantity.

Thus I have a cargo that should be placed on the stock at the single cell in the specific quantity.

The form should look like:

  • on the right side a table controlTable Control with all cargo’s,
  • on the left side another table controlTable Control with all stock cells,
  • somewhere near a textfield that should be filled with quantity.

The reason why I want use a table control Table Control is an append ability. It’s very convenient to add a cargo right here and right now, then select it and that’s it.

Is it possible to do it like that?

#2

@idsoft When editing a question, please add on to the question and signify that it is edited information. Replacing the entire contents,which you did, with different information makes the answers look foolish as they don’t correspond to the question.

Even after all that, it appears, as per your comment, the real question had to do with actually locating the Table Control in the tools. Your question does not elude to this.

#3

@idsoft, Is “a cargo”, like a truck’s cargo load? Are you trying to figure out how to load a truck? Or what exactly is it you’re trying to do?

#4

@EasyTrieve, “a cargo” like “Item”, “goods”, “parcel”. I’m trying to make a convenient GUI for put cargo to the stock cell . That’s it.

#5

Ok, now what’s a “stock cell”?

#6

Hello,

It appears there is a problem with your understanding of a many-to-many relation is.

First see this post → Should I use a separate table? which gives a simple example of a many-to-many relationship.

Now your example seems different. What it appears you want is to separate portions of a cargo into stock cells whereas the the total of the cargo is to be divided among these cells. A relational database is meant to associate records with other records and not portions of records to another. For this you would need other mechanisms (most likely a specialized macro) to control that the total placed in the various stock cells did not exceed the actual cargo or maybe a query totaling specific cargo items in stock cells & displaying this value on the form. This last suggestion requires human intervention but no macro needed.

What it seems here is:

      Cargo  (quantity 750) is split into
                                   stock cell 1,1,2    (quantity 100)
                                   stock cell 2,3,12   (quantity 300)
                                   stock cell 2,3,12   (quantity 350)

This is just a one (Cargo) to many (stock cell) relation.

As to a complete resolution of the problem presented, much more analysis is needed on the entire process involved such as what can be in stock cells or what constitutes a cargo and more. This type of issue is not meant for the Forum.

#7

Hello, Ratslinger. Thank you for your answer.
Besides that this data organization provides a split quantities to separate cells also it allow to put different cargo’s in one cell, because cargoID and cellID is a composite primary key. Also there is no way to put one cargo type into particular cell twice, only increase a quantity. It’s quite conveniently.
But the question wasn’t about a data organization. It was about table controls and using them for choosing cell and cargo.

#8

Your last sentence seems to indicate you did not look at the Link in my answer very closely. It shows how this is done - Table 3 in the first example is the many-to-many relation you are looking for. Simply add Quantity field on to it.

The remainder of the question deals with a fault in the process and why, in my estimation, a different approach is needed. Simply put, with the many-to-many the cargo in the cells can exceed the quantity in the cargo. Further checks are needed.

#9

Will post sample of many-to-many in answer later today.

#10

OK, It’s total misunderstood. “Table Control” is a control from panel “More controls”. I know how to make a many-to-many relation. I need organize my entry data form with “Table Control”, two of them.

#11

Was that the real question? You couldn’t find the Table Control. Did you look in documentation? You still have the mentioned problem. Also you do not need three table controls - just one. It would contain: 1) Unique ID; 2) List Box linking back to Cells; 3) List Box linking back to Cargo; 4) Quantity.

#12

It’s quite offensive that you find me so stupid. Anyway, the question was written in terms of “LOBase”- form is the “Form” and so on. Looks like people read and realize my question some-how else. Sure it’s my poor English. The reason why I don’t want use “list box” inside “table control” for choosing something was written in the question. In addition to it: if I have a thousand stock cells do you realy sure that it a right GUI way to select something? Whatever there is no way to make freindlyGUI

#13

I mean there is no way to make a friendly GUI in LOBase ofc.

#14

It is understood as to why a table control is desired for Cargo - to add new cargo items. However, choosing an item, such as Stock Cells, from a list box is MUCH faster than using a table control.

I certainly disagree about your statement in creating a friendly interface in Base. It may take more effort, but can be done.

#15

@idsoft, I have a couple of databases which use a list box inside a table control to select from over a thousand items from a 2nd table and it works well. I even have one database where I have multiple fields in the table control, all for the same field in the underlying record, but each list box sorted differently so I have more than one way to select a record to attach. For example, one is sorted by name, another sorted by city and name, and a third sorted by creation date descending.

#16

@idsoft, Also you might want to make sure you’re fields are appropriately indexed for optimal speed.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.