1 Topic by CDB (edited by CDB 2020-04-20 03:17:01)

  • CDB
  • Member
  • Offline
  • Registered: 2019-12-16
  • Posts: 370

Topic: Schema help or can MVD use composite keys

I am having a problem (i think) with the way MVD allocates the primary key ID. Is there any way from within MVD to have a 'composite ' key?


My problem:


I need to allow for a product to have multiple suppliers.


I would like to display in a tablegrid:


The person who ordered a part: The supplier Name : Order date: Product: Qty ordered. (This is just the basic  list of fields).
The current set up fails when I try to include the supplier name (or anything from that table). The error is that that column does not exist.  I get the same error if I directly type the SQL into DB Browser for SQLite.


Currently I have an intermediate table that contains the product_Id from the products table and the supplier_Id from the suppliers table, both set as a foreign key.


I think my problem might be that I need to actually have the supplier_id in the intermediate table as the primary key or some form of composite key.


The SQL that MVD produces is:

SELECT DISTINCT "part_suppliers"."supplier_name", "orders"."date", "orders"."order_ref","products"."part_num","technicians"."f_name","orders"."id","orders"."id_technician"

FROM "orders" LEFT OUTER JOIN "technicians" ON "orders"."id_technicians" = "technicians".id

I'm wondering if to accomplish what I'm hoping for, I need to manually create the tables and SQL scripts?

The schema I'm using is here http://myvisualdatabase.com/forum/viewtopic.php?id=6190

On a clear disk you can seek forever

2 Reply by derek (edited by derek 2020-04-20 22:27:19)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,675

Re: Schema help or can MVD use composite keys

Hi CDB,
The problem is (as mentioned in an earlier post) that you have no direct link between part_suppliers and the order_products.
The link is indirect (order_products to products (using id_products) and then products to part_suppliers (using id_part_suppliers).  That is why you get the error message (column does not exist).
If you don't want to create a relationship between product_supplier and orders, the simplest work-around might be to use a calculated field to do the link across for you.
If you could attach your project, I could have a look and see.
I'm not clear on the actual ordering process (I'm presuming it's done by the technician).  Does he enter your own product id to get a list of possible suppliers and then selects one (which could then be used to populate orders.id_supplier, for example) or does  he know in advance what supplier he want to order the product from?  And I'm presuming it's multiple order_products lines per order?
Derek.

3 Reply by CDB (edited by CDB 2020-04-21 05:19:14)

  • CDB
  • Member
  • Offline
  • Registered: 2019-12-16
  • Posts: 370

Re: Schema help or can MVD use composite keys

Hi Derek,

I have almost solved the problem apart from duplicate entries. I think i need a 'group by' or a 'where' clause to stop that problem. Or of course I've just got the whole thing wrong!


The way I intend this to work is the Tech will look up the internal part number (LineNumber/description ) this will link to all the suppliers for that part. Then they will choose which supplier (if that part has multiple suppliers and the correct supplier part code) and the quantity they require.


The person that actually places the order will fill out the PO/Req number (Reference).


When the parts arrive the Tech in charge of part distribution will look up the supplier; enter qty received, and then distribute it to the tech who requested the part.


The grid i have in place is for an overall view and eventually the selection boxes and text edit are for searching.


There will be extra forms for data entry and part information. A third tab or form will list by searching for a Parent part and listed below will be all the child parts that can be used with their Line number and supplier part number and picture.


That is the intention of the complete project.  For the moment I just need to get the overall view grid working, and the rest I hope will fall into place.


I should add in case you are wondering - the line number 9999999 is for those parts that have not been allocated a company line number of which there are many!

Post's attachments

Attachment icon cdb_test_v1.zip 348.51 kb, 226 downloads since 2020-04-21 

On a clear disk you can seek forever

4 Reply by derek (edited by derek 2020-04-21 11:28:29)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,675

Re: Schema help or can MVD use composite keys

Have you got any data to go with the program (there's no sqlite.db with the download) - it's hard to know what's supposed to be happening otherwise.
Derek.

5 Reply by CDB (edited by CDB 2020-04-21 11:35:20)

  • CDB
  • Member
  • Offline
  • Registered: 2019-12-16
  • Posts: 370

Re: Schema help or can MVD use composite keys

Sorry about that Derek, I thought I'd included everything.

File now attached.

Sigh, must remember to click 'add file'.

Post's attachments

Attachment icon sqlite.db 23 kb, 223 downloads since 2020-04-21 

On a clear disk you can seek forever

6 Reply by derek (edited by derek 2020-04-22 00:26:53)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,675

Re: Schema help or can MVD use composite keys

Hi,
I couldn't make much progress using your program (it seems to be just a grid with the duplicated rows, so not sure how you entered the data in the first place) and I wasn't sure about parts of your schema.
So instead, i knocked up a simplified version of what I understand your requirements to be from your posts (please see attached), and maybe there will be some things in there that you can take and apply to your project.
I've oversimplified a few things in an effort to get the basics right (working on the principle that if they're not right, then it's never going to work properly).  Once you're happy with that, you can build up the complexity from that point.
I'm also not sure why you need a separate 'productdetails' table - it appears to have a 1:1 relationship with the 'products' table and, as such, why not just include the fields on 'productdetails' in the 'product' table.
Anyway, hope some of this might move you forwards.
Derek.

Post's attachments

Attachment icon cdbschema.zip 342.69 kb, 246 downloads since 2020-04-22 

  • CDB
  • Member
  • Offline
  • Registered: 2019-12-16
  • Posts: 370

Re: Schema help or can MVD use composite keys

Hello Derek,

That is well on the way to what I'm trying to do. Thank you very much.

I'm determined to work out why my schema doesn't work once I add the Technician name into the grid.

I'll have to try it out in mySql to work out why adding the tech name creates duplicate entries.

On a clear disk you can seek forever
  • CDB
  • Member
  • Offline
  • Registered: 2019-12-16
  • Posts: 370

Re: Schema help or can MVD use composite keys

Hi Derek,

Just a quick question, how did you make the drop down boxes embed into the table grid on the Supplier parts X-ref tab?

On a clear disk you can seek forever

9 Reply by derek (edited by derek 2020-04-27 10:35:28)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,675

Re: Schema help or can MVD use composite keys

Hi CDB,
It happens when the tablegrid is made editable (see screenshot) and there is a relationship between the main table in the tablegrid and other tables.  The same thing (dropbdown boxes) also would happen with date/time fields or numeric fields.
I mainly use editable tablegrids for 'reference' tables (for want of a better term) - in any project, there will typically be quite a few of them and to create separate forms for listing and inputting more than doubles the time to develop  (and the number of forms, so the program takes longer to load etc). 
They're not as user-friendly as an input form you've designed yourself nor are they as easy to validate what is being entered therefore it's something that I'd only give 'super users' or administrators access to, but typically, once they've been set up, they're not changing frequently.
For procedures that are being accessed regularly, I'd always go with dedicated tablegrid and input forms.
Derek.

Post's attachments

Attachment icon screenshot.jpg 199.1 kb, 96 downloads since 2020-04-27