Added database looks empty

Software
1

Hi,

I’m trying to migrate our company’s parts database to KiCAD, for now I’m playing with a simple case of one table but cannot get that to work.

The library shows in the part chooser, but is empty. What am I missing here?

The database

image
image813×111 4.24 KB

Here’s what it looks like in the Chooser
image

The dbl file

{
    "meta": {
        "version": 0
    },
    "name": "Camero parts",
    "description": "A database of Camero components",
    "source": {
        "type": "odbc",
        "dsn": "",
        "username": "",
        "password": "",
        "timeout_seconds": 2,
        "connection_string": "DSN=Camero_parts;"
    },
    "libraries": [
        {
            "name": "Capacitors",
            "table": "Capacitors",
            "key": "Mnf. PN",
            "symbols": "Symbol",
            "footprints": "Footprint",
            "fields": [
                {
                    "column": "CameroPN",
                    "name": "Camero PN",
                    "visible_on_add": true,
                    "visible_in_chooser": true,
                    "show_name": true
                }
            ],
            "properties": {
                "description": "description"
            }
        }
    ]
}
2

Technical note about this forum: format all code as “Preformatted text”, otherwise the forum software messes up many characters.

3

It looks like the DSN must be set up ok as KiCad will bork if it can’t connect to the database so that’s a good start.

    "libraries": [
        {
            "name": "Capacitors",
            "table": "Capacitors",
            "key": "Mnf. PN",
            "symbols": "Symbol",
            "footprints": "Footprint",
            "fields": [

Two things I would look at in your .kicad_dbl - firstly I would change the name of your key column in the database to something without a space. “Mnf. PN” is probably not a good name. I have had to remove spaces from database column names before for them to be recognised.

In any case, I would have used the “CameroPN” as the key - you want to be able to substitute parts, the house part number is the point of reference and you validate your components against that definition. i.e. Your CC6308 could be substituted by any other suitable and validated 10000pF 50V X7R 0402 not just a GRM155R71H103KA88J.

Secondly, you need to add some more columns for your other items. MPN & Description at least but you probably want to add some values for the database to start to become useful.

    "libraries": [
        {
            "name": "Resistors_SMD",
            "table": "RESISTORS_SMD",
            "key": "part_id",
            "symbols": "Symbols",
            "footprints": "Footprints",
            "fields": [
                {
                    "column": "Description",
                    "name": "Description",
                    "visible_on_add": false,
                    "visible_in_chooser": false,
                    "show_name": true
                },
                {
                    "column": "Datasheet",
                    "name": "Datasheet",
                    "visible_on_add": false,
                    "visible_in_chooser": true,
                    "show_name": false
                },
                {
                    "column": "Name",
                    "name": "MPN",
                    "visible_on_add": false,
                    "visible_in_chooser": true,
                    "show_name": true
                },
                {
                    "column": "Value",
                    "name": "Value",
                    "visible_on_add": true,
                    "visible_in_chooser": true,
                    "show_name": false
                },
                {
                    "column": "Manufacturer",
                    "name": "Manufacturer",
                    "visible_on_add": false,
                    "visible_in_chooser": true,
                    "show_name": false
                },
                {
                    "column": "Tolerance",
                    "name": "Resistance Tolerance",
                    "visible_on_add": true,
                    "visible_in_chooser": false,
                    "show_name": false
                },
                {
                    "column": "Power_Rating",
                    "name": "Power Rating",
                    "visible_on_add": false,
                    "visible_in_chooser": true,
                    "show_name": false
                },
                {
                    "column": "Case_Package",
                    "name": "Case/Package",
                    "visible_on_add": true,
                    "visible_in_chooser": false,
                    "show_name": false
                },
                {
                    "column": "Voltage_Rating",
                    "name": "Voltage Rating",
                    "visible_on_add": false,
                    "visible_in_chooser": false,
                    "show_name": false
                },
                {
                    "column": "Stock",
                    "name": "Stock",
                    "visible_on_add": false,
                    "visible_in_chooser": true
                },
                {
                    "column": "part_id",
                    "name": "ID",
                    "visible_on_add": false,
                    "visible_in_chooser": true
                }
            ]

Screenshot 2025-01-19 at 17.33.02
Screenshot 2025-01-19 at 17.33.021920×1173 263 KB

4

How does this swapping work? I thought that the Key field should be unique, and the inhouse PN is the same for all alternate parts.

5

Thank you for your reply, I’ve used the example you gave and dropped the description property. The library stopped appearing empty at least.
image
I’ll try to play with it some more.

I know that the library lacks the info to be useful, I purposefully stripped it down to bare minimum to get to the root cause.

6

OK, my guess is KiCAD does not like any non-letter symbols, even the _.
Got the thing to almost work, no description though.

{
    "meta": {
        "version": 0
    },
    "name": "Camero parts",
    "description": "A database of Camero components",
    "source": {
        "type": "odbc",
        "dsn": "",
        "username": "",
        "password": "",
        "timeout_seconds": 2,
        "connection_string": "DSN=Camero_parts_new;"
    },
    "libraries": [
        {
            "name": "Capacitors",
            "table": "Capacitors",
            "key": "CameroPN",
            "symbols": "Symbol",
            "footprints": "Footprint",
            "fields": [
                {
                    "column": "MnfPN",
                    "name": "Manufacturer PN",
                    "visible_on_add": true,
                    "show_name": true
                }
            ],
            "properties": {
                "description": "description"
            }
        }
    ]
}

image
image

7

Ok, so here’s the deal. KiCAD has to be restarted in order for the changes to really take place. And that’s it.

If column has a dot (Mnf. PN for me), it is not recognized, but it’s totally fine with spaces (changed it to Mnf PN and it started showing).

image
image749×117 3.54 KB

1 Like
8

Glad you got it working. Yes, should have mentioned restarting KiCad after a change to your .kicad_dbl.

Usually you want to key on a unique component entity but the actual component used can be managed elsewhere in your EPR. So, for a specific microcontroller with a particular footprint with a specific performance you choose that and only that part. Nothing else will do. In other situations you might need a 10k 0805 resistor and don’t really mind who’s the manufacturer. Your database entry should reflect the least restrictive option. If you need that specific microcontroller you specify that with no substitution possible. For your standard pull up resistor, you specify a the footprint and value but allow substitution. If you tie your database to your EPR system, you can use that to manage substitutions. The database key can either link to a non substitutable part or you can use the EPR to choose an alternative component/supplier if Mouser or whoever somehow happen to be out of Yageo 10k resistors.

9

We don’t really have that option. If a component is substitutable, it will have the same PN with an ‘Alternate’ property, in that very table.
So I went with Mnf PN, as at least these tend to be unique.

I could add a suffix to the PN (CC6308-a or whatever), not sure if this will be the right thing to do.