Link parts property to a database

3D Design Library
1

Hello

 

Let me tell you my problem: in my company they use a software (quadratus) that groups all the rooms with different property fields included.

This software was already present before the integration of solidworks. So I integrated these properties (from the quadratus software) into each of my 3D pieces by hand.

Circumstances mean that certain fields will be changed or may be changed, such as prices.

 

That is where my question comes from. Can we link the properties of the parts to that of my 3D parts by an excel file from the quadratus software.

 

I know that there is solidworks EPDM that would do this well but it would imply changing software which is not very possible.

 

Thank you for your help.

 

 

Kind regards

 

Florian HALLUIN

2

Hello

I hope you don't store the prices of your parts in the SW properties of your parts! In which case I would like to suggest that you try to review your organization.

On the other hand, you can develop something if you practice programming. But know that in any case it will be long and complicated. But interesting....

1 Like
3

Yes, the prices are stored in the database of all components (under quadra tus) and in my Solidworks parts.


I am open to any change.

To tell the truth, my company acquired Solidworks almost a year ago for my DUT internship during which I partly integrated the software into the company. Being alone in this task, I tried to do things in the best way while knowing that I could make mistakes... This year I am on a work-study program and I continue to improve the integration even if it is becoming more and more complicated given the number of documents already created.


I don't mind doing programming either, I have some basics to see depending on what I should do.

4

The problem that remrem certainly wants to raise is that if you store the price of your part in your SW share, if you share it with your suppliers or customers, they will know its price...

Unless that's what you're looking for...

a+

 

5

On this side there is no problem because the price is just to come out with a bill of materials and have an estimate of the price of the assembly in-house only.

6

AH! ;-)

For several months now, we have been implementing complete bills of materials and therefore the relationship between our ERP and SW.

Our SW files only include technical information (material, thickness, etc.) and then we have set up a file exchange (in the form of txt) or we exchange with the ERP. The sales and purchase information (Price, supplier, etc.) is thus only in the ERP and the link is made by a common property (with us the article code).

Personally, I made a small application in vb.net that updates the information in the ERP from SW by writing directly to the database (MySQL).

It's a big project, so I advise you to take stock of the needs of the services concerned. To visit other companies to see how they work. To set priorities.

For the time being, I advise you to consider only one way to update, we have chosen SW  => ERP. Thus, if the revision of a document in SW is modified, it goes back to the ERP. The advantage is that if we change the price in the ERP, we don't need to update it in SW since it is only stored in the ERP. Similarly, if you change the thickener in a room, it does not go back to the ERP because it is not stored there. Hence the interest in choosing the right information you need in the ERP.

Hoping I helped you a little... ;-)

2 Likes
7

(I have the impression that my answer has not been published so I type it again...)

What you say is interesting.

In my company that does water treatment, we mainly assemble standard products: tubes, elbows, tees, valves etc.

So in this case it would be more the ERP that would communicate to SW.

 

Would it be possible to know a little more about your program? How txt file exchange works. If it's automatic, all the parts at once, one by one?

 

I also thought about seeing how other companies did it, but would they open the doors for me so easily?

8

In fact I have two apps:

           1. SW = ERP > link: Information written directly in the ERP database

Every morning (Example) a task starts automatically.

It reads revisions in SW files (without opening them) and compares with the ERP. Then updates if necessary. Processing time: ~10 minutes

Created a thumbnail (image), pdf plan and and an Edrawing of each SW element whose article code is present in the ERP and inserts the links to these elements. Thus, a user has direct access in the ERP to a representation of the part, its PDF plan and a 3D edrawing.

 

           2. Creating BOMs

When opening an Excel spreadsheet, after many processes and error checks. I create one or more txt files that the ERP knows how to process to then create items or create ranges and manage the supplies.

 

As far as companies are concerned. We had the chance to meet several companies who welcomed us and advised us in our project.

1 Like
9

It seems to be a pretty good solution. Would it be possible to have access to the program code?

So I'm going to see if I can meet some companies to see how they work. Hoping that they will be as welcoming as the ones you met.

10

Good evening

If I had this type of project to do, I would start by defining a (well-defined) specification with my company.

Then I would look to see if I have access to the database structure of the target software.

If so, I use SQL queries to modify or add the data.

If not, you have to find another way to inject the data (text file with separators, excel file, XML file,...)

Then, you have to choose whether to create a separate software, an SW add-in or both.

I have bolded both, because depending on the frequency of updates, you need both.

Let's take the following example: you change a property price of a room and a salesperson makes a quote to sell the same room at the same time as you. The salesperson's quote will be based on the old price until the next day's update. (Not really cool, either way).

I'd even better see a windows service that listens to SW. A document is opened, edited, and closed. Upon closing, the service will check in the target software and make the changes if there are any. In this case, there is only one software to create: the service.

After all, the code itself is not very complicated, retrieving the values of custom properties is simple and reading data in a database is just as simple.

So nothing too bad to code but a lot of work on the specifications.

2 Likes
11

Hello

Thanks for the extra help.

 

So I will as everyone has advised me, draw up specifications for this project.

 

On the other hand, I have some basic programming but not enough to release a code like that.

You tell me that retrieving the values of custom properties is simple but could I have a clue on how to proceed? What function is used?

Under which software would this project be most suitable to develop?

12

For my part, I would go for a Macro that would make the link between your Excel file and your Solidworks files

 

Together to program via VBA in an Excel file you have it stamped on the quadratus extraction and you loop on each line to check each property of each part and if necessary modify them

1 Like
13

Hello 

I'll give you an example tonight in the form of a macro.

For programming software, I use Visual studio 2017. But there are many others.

Choose your programming language carefully, equip yourself with a good pen and a ream of paper. When you run out of paper, your project should work. :)

 

1 Like
14

Not all parts of the Quadratus base are represented in 3D. I can't see if this is going to be a problem.

I think I may update the SolidWorks properties of parts only when you open an assembly or a part.

 

I just saw some lines of code that I think will be useful to me:

" To recover a property, the instruction is:

                SWmoddoc. GetCustomInfoValue("", "code")

We get the "code" property in the VBA variable "CODE", where SWmoddoc represents your active document (Set SWmoddoc = swApp.ActiveDoc). "

15

It doesn't matter if all the parts in the ERP don't have 3D. You just have to compare the two on a single field.

For me, updating the data only when opening a part is not enough because it would mean that the data has been modified on a closed file if the binding direction is Sw>>ERP.

The easiest way to retrieve the values of custom properties is to use the propertymanager object. That's what he's made for. The advantage is that it offers a collection of objects and as a result, you can do an update by lot and not property by property. You just have to make a datatable converter into a property collection. You also need to create a comparator or class that implements ICompare().

 

1 Like
16

In my case the link would be ERP => SW only.

Is there any help to know how to retrieve the values of custom properties through propertymanager?

I'm having a hard time figuring out how to do everything you tell me

17

Yes, there is a help, that of SW or Google APIs.

If you have trouble understanding what I'm telling you, take a pen and paper, write down what you want to do and it will become clearer:)

The idea is simple:

Taking data from a product into the ERP

Find the corresponding sw file and open it

Update the data in the sw file

Save the sw file and close it

Do this for all ERP products

That's the basic principle.

What you can do by going through a service is:

Listen to the ERP database

On a change in a "product" table

Open the sw file

Shift, save, close the sw file

With words, we immediately realize that one will process all the data of the ERP products while the other will update only when there is a need on a single sw file.

1 Like
18

I understand the logic of the execution of the problem. It's for the conversion to a programming function where I have more trouble

In the example you give me, are opening, modifying, saving and closing done in the background and very quickly? Will the processing time of the database be fast (I would say for me that 10min is fast for more than 1000 3D parts)?

 

In our case, I think that this simple logic could also work because I don't come out of nomenclature and plans without working the assemblies concerned and therefore the parts too

 

When a room is open

Look if Internal Ref exists => If not END

If yes

Search for the ref in the BDD quadratus

Check if the properties of the SW part match => If not, update and save them

              If yes END

 

I think the internal reference will serve as a liaison between SW and the ERP.

 

19

Hello

@f.halluin, I see in your profile that you are a MyCadServices customer, so no need to waste time in developments, all the tools you need already exist in the MyCad Tools and the one that will be most useful to you will be Integration which will allow you to do a mass property recovery on all your CAD database compared to an Excel file or other from your ERP.

 

@+

2 Likes
20

Thank you coyote but I don't seem to have the subscription that allows the use of mycadtool.

Also would this tool allow me to replace the SW properties with that of the ERP?