AnsweredAssumed Answered

Total quantity equation

Question asked by Filipe Venceslau on Mar 12, 2010
Latest reply on May 7, 2010 by Filipe Venceslau

Like • Show 0 Likes0
Comment • 10

Hi people!

I would like to add a column with the total quantity of parts required.

I already have column Qty. which tells me how many parts are in my assembly.

I need a column that will calculate that Qty. times a total assembly quantity.

I have a custom property (of type Number) in my main assembly, which I called "quantity", with the value of 3.

I've tried entering the following equation: 'QTY.' * $PRPSHEET:"quantity"

Which returns me a value of: ='QTY.' * 3

If I replace $PRPSHEET:"quantity" with an actual number, I do get a calculation, but anytime i refer to a custom property (even of the parts) it doesn't seem to want to calculate the value.

My conclusion is that this value is being brought over in a string format, and BOM isn't capable of recognizing it as a number, so it doesn't calculate it.

I've discussed this with solidworks support, and no one was able to give me a straight answer.

Anyone out there ever run into this problem? Any work around?

I've also tries referencing to a custom property in the drawing itself, but I always run into this problem.

Thanks!!

Ibrahim Arda May 7, 2010 12:06 PM

Correct Answer

Hello Filipe;

Yes you can link custom properties to the Excel-Based BOM.

I am attaching the pictıre and i have mentioned the linking procedure before.

" Be careful to assign your custom property to a column.

You can do it by selecting the whole column and clicking the area which usually written A1,B1, etc. at the top left

Do not assign any columns to the right of "$END" "

Also I am adding a view to explain it properly.

It is correct that the macro would not run, I have warned you , But i think that gives an idea what can be done in Excel Based BOM.

That macro serches for spesific entries in BOM and gathers Custom Property data for spesific calculations.

If you want, give me a spesific condition for BOM and I will make a short macro. Then you will develope the macro and your skills and I will help you again

(if necessary ).

I am trying to teach you how to catch fish. I am trying for your favor do not misunderstand me.

Again any questions, Please ask.

Best Regards

See the reply in context

No one else had this question

Outcomes

Filipe Venceslau Mar 26, 2010 10:30 AM

Has anyone come across this problem?

I would really appreciate some help here....

Thanks

Actions

Peter De Vlieger @ Filipe Venceslau on Apr 16, 2010 4:40 AM

I might be completely missing the point but just in case....

Ever thought about just working with an equation in a cell in the style of

=(sum(E14:E25))*x

with, for instance, E14 to E25 being the range of the cells you want to count

with x being the factor that you want it to multiply with?

Ahh.... I just noticed you mentioning that you could when using a number for x but not when you wanted to use your $PRPSHEET:"quantity"

Actions

Filipe Venceslau @ Peter De Vlieger on May 4, 2010 10:33 AM

Hi Peter,

Thanks for your feedback.

I hadn't tried using cell formulas yet, but just tried, and I get the same result, can never get it to multiply to a custom property in my main assembly.

Thanks!

Actions

Ibrahim Arda @ Peter De Vlieger on May 4, 2010 1:07 PM

Hello Peter;

Your advice is the fastest way of BOM calculation, I recommend yours if this Felipe's one is a normal calculation.

My advice is useful when you have to make different calculations with the data gathered from the cells when the entities are not regularly lined up. Eg. A1 affects the result of B1 by 1/8 and the result of B1 must be added to C1 and all must be calculated in local currency bla,bla,bla... The macro gives more expandable calculation workspace the way than normal cell formulas did.

Best Regards

Actions

Ibrahim Arda Apr 29, 2010 12:07 PM

I have solved the problem with a different and designated Excel design table template.

In this method you can add glue,paint,sand resin,etc. according to the other properties.

Just make adifferent Excel BOM Template for your BOM. Remember to change the "Cell Properties" in Excel (The including data is number).

Be careful to assign your custom property to a column. You can do it by selecting the whole column and clicking the area which usually written A1,B1, etc. at the top left

Do not assign any columns to the right of "$END"

Everytime you update the BOM you will get a new Excel table calculation.(Yes it really updates, some say don't)

You can add also macros to make complex calculations. If you have example file or spesific info. I will help you more accurate answer.

Best Regards;

Actions

Filipe Venceslau @ Ibrahim Arda on May 4, 2010 10:40 AM

Hello Ibrahim,

Thank you for your reply.

I had never noticed there was a dedicate excel based solidworks BOM, seems like this may work. Especially considering I can trigger macro functions with it (NOW this starts to have some REAL POWER!).

Do you have a BOM template you can upload so I can see how you made it please?

Thank you

Actions

Ibrahim Arda @ Filipe Venceslau on May 4, 2010 12:58 PM

Hello Filipe;

Please check the file attached. I am sorry that I have cropped down the %90 of the macro but the rest gives the idea. You will find a macro called "Hesapla" (means calculate) this macro searches for terms in designated cells and makes the calculations necessary.

Attachments

bomtemp-Maliyet Formu-Sample File.xlsm50.2 KB

Actions

Filipe Venceslau @ Ibrahim Arda on May 5, 2010 1:20 PM

Ibrahim,

I'm a little confused. Can I also link custom properties to this excel-based BOM? How would I do that? Exactly what is your macro supposed to be calculating? It doesn't run on my computer, due to chunks of programming missing (some GOTO's are missing).

Filipe

Actions

Ibrahim Arda @ Filipe Venceslau on May 7, 2010 12:06 PM

Hello Filipe;

Yes you can link custom properties to the Excel-Based BOM.

I am attaching the pictıre and i have mentioned the linking procedure before.

" Be careful to assign your custom property to a column.

You can do it by selecting the whole column and clicking the area which usually written A1,B1, etc. at the top left

Do not assign any columns to the right of "$END" "

Also I am adding a view to explain it properly.

It is correct that the macro would not run, I have warned you , But i think that gives an idea what can be done in Excel Based BOM.

That macro serches for spesific entries in BOM and gathers Custom Property data for spesific calculations.

If you want, give me a spesific condition for BOM and I will make a short macro. Then you will develope the macro and your skills and I will help you again

(if necessary ).

I am trying to teach you how to catch fish. I am trying for your favor do not misunderstand me.

Again any questions, Please ask.

Best Regards

Actions

Filipe Venceslau @ Ibrahim Arda on May 7, 2010 12:17 PM

Thank you my friend!

I shall give it a try, and if I run into any problems, I'll know who to ask.

Regards,

Filipe

Actions

10 Replies

Filipe Venceslau Mar 26, 2010 10:30 AM
Mark Correct
Correct Answer
Has anyone come across this problem?
I would really appreciate some help here....

Thanks
Like • Show 0 Likes0
Actions
- Peter De Vlieger @ Filipe Venceslau on Apr 16, 2010 4:40 AM
  Mark Correct
  Correct Answer
  I might be completely missing the point but just in case....
  
  Ever thought about just working with an equation in a cell in the style of
  
  =(sum(E14:E25))*x
  
  with, for instance, E14 to E25 being the range of the cells you want to count
  with x being the factor that you want it to multiply with?
  
  Ahh.... I just noticed you mentioning that you could when using a number for x but not when you wanted to use your $PRPSHEET:"quantity"
  Like • Show 0 Likes0
  Actions
  - Filipe Venceslau @ Peter De Vlieger on May 4, 2010 10:33 AM
    Mark Correct
    Correct Answer
    Hi Peter,
    
    Thanks for your feedback.
    I hadn't tried using cell formulas yet, but just tried, and I get the same result, can never get it to multiply to a custom property in my main assembly.
    
    Thanks!
    Like • Show 0 Likes0
    Actions
  - Ibrahim Arda @ Peter De Vlieger on May 4, 2010 1:07 PM
    Mark Correct
    Correct Answer
    Hello Peter;
    
    Your advice is the fastest way of BOM calculation, I recommend yours if this Felipe's one is a normal calculation.
    
    My advice is useful when you have to make different calculations with the data gathered from the cells when the entities are not regularly lined up. Eg. A1 affects the result of B1 by 1/8 and the result of B1 must be added to C1 and all must be calculated in local currency bla,bla,bla... The macro gives more expandable calculation workspace the way than normal cell formulas did.
    
    Best Regards
    Like • Show 0 Likes0
    Actions
Ibrahim Arda Apr 29, 2010 12:07 PM
Mark Correct
Correct Answer
I have solved the problem with a different and designated Excel design table template.
In this method you can add glue,paint,sand resin,etc. according to the other properties.
Just make adifferent Excel BOM Template for your BOM. Remember to change the "Cell Properties" in Excel (The including data is number).

Be careful to assign your custom property to a column. You can do it by selecting the whole column and clicking the area which usually written A1,B1, etc. at the top left
Do not assign any columns to the right of "$END"

Everytime you update the BOM you will get a new Excel table calculation.(Yes it really updates, some say don't)

You can add also macros to make complex calculations. If you have example file or spesific info. I will help you more accurate answer.

Best Regards;
Like • Show 0 Likes0
Actions
- Filipe Venceslau @ Ibrahim Arda on May 4, 2010 10:40 AM
  Mark Correct
  Correct Answer
  Hello Ibrahim,
  
  Thank you for your reply.
  I had never noticed there was a dedicate excel based solidworks BOM, seems like this may work. Especially considering I can trigger macro functions with it (NOW this starts to have some REAL POWER!).
  Do you have a BOM template you can upload so I can see how you made it please?
  
  Thank you
  Like • Show 0 Likes0
  Actions
  - Ibrahim Arda @ Filipe Venceslau on May 4, 2010 12:58 PM
    Mark Correct
    Correct Answer
    Hello Filipe;
    
    Please check the file attached. I am sorry that I have cropped down the %90 of the macro but the rest gives the idea. You will find a macro called "Hesapla" (means calculate) this macro searches for terms in designated cells and makes the calculations necessary.
    Attachments
    bomtemp-Maliyet Formu-Sample File.xlsm50.2 KB
    Like • Show 0 Likes0
    Actions
    - Filipe Venceslau @ Ibrahim Arda on May 5, 2010 1:20 PM
      Mark Correct
      Correct Answer
      Ibrahim,
      
      I'm a little confused. Can I also link custom properties to this excel-based BOM? How would I do that? Exactly what is your macro supposed to be calculating? It doesn't run on my computer, due to chunks of programming missing (some GOTO's are missing).
      
      Filipe
      Like • Show 0 Likes0
      Actions
      - Ibrahim Arda @ Filipe Venceslau on May 7, 2010 12:06 PM
        Unmark Correct
        Correct Answer
        Hello Filipe;
        
        Yes you can link custom properties to the Excel-Based BOM.
        I am attaching the pictıre and i have mentioned the linking procedure before.
        
        " Be careful to assign your custom property to a column.
        You can do it by selecting the whole column and clicking the area which usually written A1,B1, etc. at the top left
        Do not assign any columns to the right of "$END" "
        
        Also I am adding a view to explain it properly.
        
        It is correct that the macro would not run, I have warned you , But i think that gives an idea what can be done in Excel Based BOM.
        That macro serches for spesific entries in BOM and gathers Custom Property data for spesific calculations.
        
        If you want, give me a spesific condition for BOM and I will make a short macro. Then you will develope the macro and your skills and I will help you again
        (if necessary ).
        
        I am trying to teach you how to catch fish. I am trying for your favor do not misunderstand me.
        Again any questions, Please ask.
        
        Best Regards
        Like • Show 0 Likes0
        Actions
        Filipe Venceslau @ Ibrahim Arda on May 7, 2010 12:17 PM
        Mark Correct
        Correct Answer
        Thank you my friend!
        I shall give it a try, and if I run into any problems, I'll know who to ask.
        
        Regards,
        
        Filipe
        Like • Show 0 Likes0
        Actions

Total quantity equation

Outcomes

Attachments

Related Content

Recommended Content