Erik wrote:
... copy the directory ledgersmb/old/bin to /usr/share/ledgersmb/old/
fmiser wrote:
Woo-hoo! That works!
Erik wrote:
Great! I hope the Debian project releases a fixed binary package soon!
After adding a single part, I tried batch importing using "import inventory" but am not finding much info on how the CSV file should be arranged.
Right. I'm sorry to have to point you to the source code instead of to nicely written documentation, but the columns for each import are here: https://github.com/ledgersmb/LedgerSMB/blob/master/lib/LedgerSMB/Scripts/imp...
When I search for a part, there is a choice for CSV export. That has the fields as: "Part Number","Description","On Hand","Unit","Sell Price","Last Cost","Order"
When I try that file, I get this error "Can't use an undefined value as a subroutine reference at /usr/share/ledgersmb/bin/../lib/LedgerSMB/Scripts/import_csv.pm line 517"
Hmm. That begs for smarter error reporting. Truth of the matter is that inventory importing is somethnig that only works on existing parts.
I came to that conclusion - after I sent the email. I did find the import_csv.pm file and made a csv with just those three columns and got an error about "can't find part number".
Then I found the post <https://archive.ledgersmb.org/ledger-smb-users/msg08412.html>
It suggests "the upload has 3 columns: part number, on-hand and the purchase price." That didn't work either. The same error as above.
My error here was I did not have a header row.
Is there a way for me to import my existing inventory? Other than creating parts one at a time?
Well, not at the moment.
I'm totally seeing where you're coming from and I think I can quite quickly develop an extention to the import script to allow you to import parts(goods), services and labor/overhead.
That could be really handy!
Do you need to import assemblies too?
I don't need assemblies. The old SQL-Ledger had troubles with assemblies so I don't have them anymore. I did additional digging and found how I can populate a postgres table from a .csv file. I have not tried that - but I'm concerned that if I were to do that there would be problems caused by how much the pieces of LedgeSMB link together. For example, what would happen with "cost of goods sold" if all the goods show up without purchase. So does the import script currently do more than just fill in the "parts" table? Maybe I could use psql to fill in the table with everything but "qty on hand" and "purchase price". Then use the existing inventory update process. Then what about customers and vendors (contacts)? Is there a way to get 200 customers and 120 vendors in - again without adding them one at a time? In import_csv.pm it looks like I can import invoices - but not the contacts. And in looking at the SQL tables, it appears the info is not all on one table. That doesn't look hopeful for a simple and quick import via psql. Thanks for all the hints so far! -- f
Hi, On Thu, Dec 26, 2019 at 10:12 AM fmiser <fmiser@gmail.com> wrote:
I'm totally seeing where you're coming from and I think I can quite quickly develop an extention to the import script to allow you to import parts(goods), services and labor/overhead.
That could be really handy!
Do you need to import assemblies too?
I don't need assemblies. The old SQL-Ledger had troubles with assemblies so I don't have them anymore.
Ok. That simplifies matters. I'll see what I can do in the coming days.
I did additional digging and found how I can populate a postgres table from a .csv file. I have not tried that - but I'm concerned that if I were to do that there would be problems caused by how much the pieces of LedgeSMB link together. For example, what would happen with "cost of goods sold" if all the goods show up without purchase.
COGS would fail, even though the On-Hand number might be non-zero.
So does the import script currently do more than just fill in the "parts" table? Maybe I could use psql to fill in the table with everything but "qty on hand" and "purchase price". Then use the existing inventory update process.
Well, the current import script imports "inventory" for existing "parts". Importing inventory means that it sets up the quantities and balance in the inventory account (for the parts specified) and sets up the desired COGS values.
Then what about customers and vendors (contacts)? Is there a way to get 200 customers and 120 vendors in - again without adding them one at a time? In import_csv.pm it looks like I can import invoices - but not the contacts. And in looking at the SQL tables, it appears the info is not all on one table. That doesn't look hopeful for a simple and quick import via psql.
The existing import system doesn't have imports defined for customers and vendors. I can see why you would say that a straight CSV import into the tables might not be easy. I think it's actually not too much work with the following information: * LedgerSMB differentiates between entities (legal entities and natural persons) and the roles they can have * Customers are entities with a customer role (vendors too) * To set up a customer, you need to set up a record in the following tables: - "entity" - "company" or "person" (entity_class doesn't really matter; you can select any -- it's not used in the system) - "entity_credit_account", with a role "customer" in the entity_class That's all there is to setting up a customer (or vendor). Assuming you want to mark some vendors/customers applicable to some taxes, you need to import records into the "eca_tax" table; the content of that table contains all taxes that are applicable for all vendors & customers. So, if vendor with entity_credit_account.id = 37 is eligible for tax posted on account.id = 42, then the row to be added to the eca_tax table is "37, 42". I can explain about the contact, localion and bank account tables, but basically, those can be set up in a second round. Because a lot of this info is relational, it's a bit hard to import it in a CSV, is what I'm thinking. Formats like YAML, XML or JSON allow more hierarchical data to be specified. For customer import, I'm thinking that might work better. What would be your view on that? -- Bye, Erik. http://efficito.com -- Hosted accounting and ERP. Robust and Flexible. No vendor lock-in.
fmiser wrote:
I don't need assemblies. The old SQL-Ledger had troubles with assemblies so I don't have them anymore.
Erik wrote:
Ok. That simplifies matters. I'll see what I can do in the coming days.
Okay! Thanks!
Maybe I could use psql to fill in the table with everything but "qty on hand" and "purchase price". Then use the existing inventory update process.
Well, the current import script imports "inventory" for existing "parts". Importing inventory means that it sets up the quantities and balance in the inventory account (for the parts specified) and sets up the desired COGS values.
I think you are saying that my idea of two-step would work. That is, use COPY parts FROM 'file.csv' DELIMITER ',' CSV HEADER; to fill in most of the information, followed by a run of the inventory import script.
Then what about customers and vendors (contacts)? Is there a way to get 200 customers and 120 vendors in - again without adding them one at a time?
The existing import system doesn't have imports defined for customers and vendors. I can see why you would say that a straight CSV import into the tables might not be easy. I think it's actually not too much work with the following information:
* LedgerSMB differentiates between entities (legal entities and natural persons) and the roles they can have * Customers are entities with a customer role (vendors too) * To set up a customer, you need to set up a record in the following tables: - "entity" - "company" or "person" (entity_class doesn't really matter; you can select any -- it's not used in the system) - "entity_credit_account", with a role "customer" in the entity_class
That's all there is to setting up a customer (or vendor).
That looks doable. Not necessarily without risk of error, though... Hmm. Maybe a spreadsheet with one tab with all the data, and other tabs that pull info from the main one that can be exported as CSV as needed for each table.
Because a lot of this info is relational, it's a bit hard to import it in a CSV, is what I'm thinking. Formats like YAML, XML or JSON allow more hierarchical data to be specified. For customer import, I'm thinking that might work better. What would be your view on that?
The beauty of csv is I can use the power of a spreadsheet to build it. And I can use text tools to manipulate various outputs from the old SQL-Ledger - including copy/paste from the browser. I'm not familiar with YAML, but XML or JSON I guess could be built from a csv, but this is getting convoluted... -- f
On Thu, Dec 26, 2019 at 6:53 PM fmiser <fmiser@gmail.com> wrote: [snip]
Well, the current import script imports "inventory" for existing
"parts". Importing inventory means that it sets up the quantities and balance in the inventory account (for the parts specified) and sets up the desired COGS values.
I think you are saying that my idea of two-step would work. That is, use COPY parts FROM 'file.csv' DELIMITER ',' CSV HEADER;
to fill in most of the information, followed by a run of the inventory import script.
Yes. Exactly.
Then what about customers and vendors (contacts)? Is there a way to get 200 customers and 120 vendors in - again without adding them one at a time?
The existing import system doesn't have imports defined for customers and vendors. I can see why you would say that a straight CSV import into the tables might not be easy. I think it's actually not too much work with the following information:
[snip]
That looks doable. Not necessarily without risk of error, though... Hmm. Maybe a spreadsheet with one tab with all the data, and other tabs that pull info from the main one that can be exported as CSV as needed for each table.
That would work, except for the fact that the key that cross-references most of the tables is generated on import of the entity and entity_credit_account tables. If you export the imported information again and use that to tie the various CSVs together, that'd work. (Note that 'entity.control_code' and 'entity_credit_account.meta_number' are meant to be used as system-independent references; you could put a value in it that you generate in your CSV which you can use to lookup the internal 'id' number later).
Because a lot of this info is relational, it's a bit hard to import it in a CSV, is what I'm thinking. Formats like YAML, XML or JSON allow more hierarchical data to be specified. For customer import, I'm thinking that might work better. What would be your view on that?
The beauty of csv is I can use the power of a spreadsheet to build it. And I can use text tools to manipulate various outputs from the old SQL-Ledger - including copy/paste from the browser. I'm not familiar with YAML, but XML or JSON I guess could be built from a csv, but this is getting convoluted...
Fair enough. Let me know how you think we can best facilitate future users who want to initialize the system with costumers/vendors based on your experience once you're a bit more into the process. -- Bye, Erik. http://efficito.com -- Hosted accounting and ERP. Robust and Flexible. No vendor lock-in.
fmiser wrote:
I think you are saying that my idea of two-step would work. That is, use COPY parts FROM 'file.csv' DELIMITER ',' CSV HEADER;
to fill in most of the information, followed by a run of the inventory import script.
Erik wrote:
Yes. Exactly.
Sweet! I'll give that a try.
fmiser asked:
Then what about customers and vendors (contacts)? Is there a way to get 200 customers and 120 vendors in - again without adding them one at a time?
Erik wrote:
The existing import system doesn't have imports defined for customers and vendors.
[snip]
That looks doable. Not necessarily without risk of error, though... Hmm. Maybe a spreadsheet with one tab with all the data, and other tabs that pull info from the main one that can be exported as CSV as needed for each table.
That would work, except for the fact that the key that cross-references most of the tables is generated on import of the entity and entity_credit_account tables. If you export the imported information again and use that to tie the various CSVs together, that'd work. (Note that 'entity.control_code' and 'entity_credit_account.meta_number' are meant to be used as system-independent references; you could put a value in it that you generate in your CSV which you can use to lookup the internal 'id' number later).
I'm not quite following that...
... Let me know how you think we can best facilitate future users who want to initialize the system with costumers/vendors based on your experience once you're a bit more into the process.
Heh. I am not sure that I count as normal enough to be used as a reference for how anyone else might be importing!! -- f
participants (2)
-
Erik Huelsmann -
fmiser