As @craftyjon inferred, I’m not advocating any native KiCad ability to look up cost data. My post was in reference to the script that @teletypeguy was developing. KiCad developing a BOM and pulling whatever fields are needed for that BOM from the parts database is a useful capability that could include part cost data. This is a fairly standard capability (altium, old orcad) that any ‘decent engineer’ should be familiar with.
That said, this has been a fruitful thread with a lot of fantastic results. Lets not pollute it further with our disagreement (if we even have any) and get back to the discussion at hand (KiCad database driven design). Lets agree to disagree and move on.
Sounds like @Antarctica may be in a similar workflow as me. I have maintained a master parts spreadsheet for years, adding parts as I need to, with primary vendor/vendorpartnum/pricing and same for a secondary vendor. A lot of manual work that I would update periodically as a new project comes together and I want board cost summarized in my bom (deleting cost columns for the assy house bom). Just did not have a good reason to improve on that workflow until now. Kicad’s database interface will let me move it all to an sqlite db, and I can do more than just place intelligent symbols now – I will be able to pull any of the db fields into a custom bom (have not got to that part yet, but sounds easy enough). I am not asking for any new kicad features, just enjoying the new features that v7 will give me as an old fart learning new tricks, grinning and saying thank you to all the awesome kicad devs!
Well, with a bit more python hacking I can pass a mouser part number and buy quantity and pull the available quantity and price at my buy quantity. Next up is poking that into an sqlite db. @eeintech – thanks for the mouser lib. I peeked through it as I was trying things out, but as a bare-metal kinda programmer I wanted to dig into the details and learn a bit more python. Have written oodles of C since the 80s but only a year or so in python land, so it is a good chance to learn more.
I thought about separating the main parts db that kicad will use and have another for mouser, probably one for digikey (don’t use them too often) which get updated as desired and then use some sort of post-processing script as @craftyjon suggested. That may well be the clean way to go, but I think I will initially try including vendor availability and cost columns in the parts db, and just running a python program to pull current info and update as desired. Will only need to run it now and then, and then I can, from within kicad, generate a bom with cost info for me and one without it for the assy house. Thinking out loud on much of this, but it is an interesting project.
I have a question about database-sourced parts and DNI (do not install, aka DNP…) parts:
In my v6 library I only had atomic parts when absolutely needed and general parts (cap, res…) simply had a value assigned in the schematic when placed, and I would set value to DNI as needed. It was easy to find in the DNI parts in the bom and deal with them.
Now as I move to a database for v7, everything is intrinsically atomic (not that there is anything wrong with that) but what to do about DNI parts? I can assign say an 0402 cap whose value is pre-defined as DNI and place as needed, but that gets clunky to do for more than a few types of parts that may be DNI. Unsure how to deal with DNI parts with the database since value will be hard-coded.
I heard about an upcoming “assembly variant” mechanism in the works somewhere and wonder how that plays into things. Should there be a DNI field for each symbol – something that is visible at bom-generation time? thx
Awesome! I see that now in 6.99. I have not used 6.99 yet except to figure out db stuff. Yes, that DNP property will be great. So just choose a feasible value part from the db (since all will be atomic), set the dnp property and hide value if desired.
Perfect. thanks Jon
I am looking forward to v7 database functionality, and am trying it out using 6.99. I have been busy pulling my parts spreadsheet info into an sqlite db. A couple of questions:
It is nice to be able to open schematic symbol properties and click the little icon to open a datasheet. It seems like the db should contain links to datasheets (I want them local, not online). If I add a database field for datasheets, does each field need to contain a full pathname like:
/home/gil/kicad/datasheets/xyz.pdf
or can it use some sort of environment var for the path to keep the db entries shorter?
I am not a database power user, though I can tweak and manage it using db-browser or sqlite-studio. Either of those will get the job done, but I am wondering what other tools people find handy for working on an sqlite database (editing, reporting…) without getting deep into sql queries and such? Any pointers appreciated.
The datasheet field gets handed off to a PDF viewer. It defaults to the system PDF viewer which on my system is Chrome, but in Preferences you can point it to a script of your choice where you could implement smarts like expanding the path, e,g, differentiating between http* URLs and filenames and do the appropriate thing. I think you can also interpolate your selected environment variable.
Edit: I did a little experiment to see if a custom script can be avoided. First I defined a path variable as follows:
KICAD_USER_DATASHEET_DIR file://<absolute path to my stash of PDF datasheets>
Next I took a symbol in my libary which is a Hitachi nixie and set its datasheet field to:
Then while editing the symbol I chose Inspect > Show Datasheet and voilà it brought up a viewer on the datasheet.
Only glitch is on my system it fired up Calibre. Probably something to do with my desktop preferences for various file sufffixes. Your results will depend on your desktop settings.
I just set up a kicad path and have the datasheet loaded from the db now. Had to add a Datasheet field to the symbol. Now that brings up another question: How to add a new field to all symbols in a library? A script perhaps, or strategic text find/replace in the .kicad_sym file?
Edit – is NOT necessary to add custom field to actual symbol (and Datasheet field is pre-defined)
Schematic Editor / File / Schematic Setup / General / Field Name Templates
If you add a name there, then it shows up as an empty field in all schematic symbols, and it get attached to any symbol for which you fill in the field.
This is not in general the way to do it for database libraries though – in most cases you would want to add the field via the database config file, not via the field name templates feature.
Was AFK there trying to make sense of all this. I thought I needed to add new fields (as defined in the database) to the actual symbol and that had major implications. But now I realize that was all nonsense and no edits are needed so ignore all that wonky rant of mine.
Now, let me see if I have the concepts and steps correct (as this seems to be how it is working with the little test files you provided, which I modified to point to my symbol and footprint libs):
all kicad standard symbols have pre-defined fields for Ref, Value, Footprint, and Datasheet.
new symbol fields can be added via mydb.sqlite and mydb.kicad_dbl files.
in mydb.sqlite, for a part table such a Resistor, a field (column) such as MPN is defined, and populated appropriately for all parts.
this new MPN field needs to be mapped properly in the mydb.kicad_dbl file to provide the symbol chooser with the linkage to the database.
the real symbol library, mylib.kicad_sym, stays in the symbol editor and the new database is available when you add mydb.kicad_dbl alongside it.
symbols are still edited in the mylib.kicad_sym library – the db library is read-only in the symbol manager.
from the schematic symbol chooser, parts are now available via the database in mydb-Capacitors, mydb-Resistors, etc, nicely divided into tables as defined in the database.
from the schematic symbol chooser, parts are still available from the original mylib.kicad_sym library – normal kicad mode.
I was able to add/commit a new field to the mydb.sqlite file, add some test data to the records, and modify the mydb.kicad_dbl file. Then I opened 6.99, created a project, placed parts on the schematic and the new field was available. If I change something in the database, the Tools/Update-Symbols-from-Library works as advertised.
So no extra edits to the symbol library as I had originally thought. I got datasheet and custom fields updating, and I am just about ready to get my production database running. I am editing and re-structuring my data in a csv file in librecalc and will import the tweaked csv into sqlite-studio to create the db. Then all changes will be in sqlite. I have all my symbols in one lib file, which keeps the chooser tidy as it is still visible, but I am going to take advantage of the db tables, as they organize the chooser nicely.
Still interested in any tips on database tools other than the db-browser and sqlite-studio with which I have been working. thx
Thanks for confirming. For a db tool, I can add/delete/tweak and maintain it with sqlite-studio pretty well, but it would be nice to find something that lets me generate some simple reports – eg: from table x, I click checkboxes for a subset of columns, and dump to a pdf for viewing/printing. Maybe with a simple condition like “if column X is null or not null, include this record for reporting…” I will peek at dbeaver.
It would actually be nice to have a way for kicad to ignore flagged records somehow (like NRND field not null), to limit the values displayed in the chooser. Example, I have started using 0402s for all my COG needs now, but I still have a batch of 0603s in my system, and need to leave them in there. A lot of chips are defined that I will not use again but need to stay in the db. Would be nice to have a visibility flag column that could be tested, but I realize that is feature creep and there are many things in the queue.
thx,gil
if you are inclined towards ‘programming languages’ i find that with a bit of SQL knowledge
you can manipulate / report db in a very efficient way, (but you can nuke them very fast as well )
I have a limited working knowledge of sql queries, but never really had an application until now. Is there a good python script to play with, for extracting data (I can hack my way through the sql queries) to output to something like pdf? Or C, C++, java. thx
you can start making practice using SQL queries even with the DB browser for SQLite,
you have a tab that enable execution of SQL command queries,
try typying:
select * from Capacitors where Footprints like '%0201%'
in the execute SQL tab…
and BTW the forun has syntax highlighting for SQL? how cool is that?
A SQL view on the database server would be one way to accomplish this. A view is basically a “virtual table” that is the result of a SQL query. So you can create a view into your Resistors table that shows only the resistors you want to appear in KiCad (maybe “not NRND” or whatever you want the criteria to be).
)