Lighting device weights and worksheets

[markdd]

Currently, weight values for Lighting devices are reported to worksheets using the measurement and the Unit (ie 42 kg). This means that the SUM function will not work. Is there a way of stripping out the "kg" or "lbs" value to just see the number, or is there a function that I should be using that ignores the Unit suffix? Any help would be really helpful.

 

Many thanks

 

Mark

[Pat Stanford]

You will probably need to use a double function something like:

 

=Value(SubString('Lighting Device.Weight',' ' ', 1))

 

SubString use these the second parameter (in my example two single quotes with a single space between them) is the delimiter. Every time there is the delimiter character in the string Substring will recognize that as a separate part. The last parameter (One (1) in my example) is which of those parts to return. So the substring above will return the part of the weight field before the first space. [I know that the record/field is probably wrong. I would have to switch workspaces to try and find the correct names.]

 

The Value converts the text returned by the substring into a number that you can do math on.

 

[markdd]

This is great Pat.

 

Slight correction to your function: 

 

=VALUE(SUBSTRING('Lighting Device'.'Weight', ' ', 1))

 

Works like a charm

 

Many thanks

 

Mark

[Pat Stanford]

I thought I probably had the quotes wrong, but figured you are good enough to get it.

 

Glad to help.

[markdd]

I would like to combine the weight values of 2 database headers to give the combined total of Lighting Pipe and Lighting Instrument. I have a feeling I need the CONCAT function, but cannot workout how to combine it with the VALUE function.

 

Can this be done?

 

Thanks

[Pat Stanford]

I don't think Concat is what you want. All that does is combine strings.

 

I think you need IF instead. Set up a database that contains both Lighting Pipes and Lighting Instruments. Use something like this:

 

=IF('Lighting Pipe'.'Weight <> '', Value(Substring('Lighting Pipe'.'Weight', ' ', 1)), Value(Substring('Lighting Instrument'.'Weight',  ' ', 1)))

 

The above says that if the weight field of the lighting pipe is not blank then use the value of the lighting pipe.weight record otherwise use the value of the Lighting Instrument.weight record.

[markdd]

Hi

 

I can't seem to make it work. I just want to combine the weights into one column so that I can get the totat weight of a lighting pipe with it's associated Lighting Devices. Here is what I have so far just to give you context. (i've attached the file as well

 

 

Thanks Pat

 

Mark

Weight Field.vwx

[Pat Stanford]

Put this formula into cell E2

 

=IF('Lighting Device'.'Weight'<>'', C2, D2)

 

Once you get that working (tested here), you can replace C2 and D2 with the actual formulas from those cells so you don't need the extra columns.

 

 

[markdd]

Thanks Pat

 

I think this will work. I'm going to work on this some more tomorrow....

 

Just out to get my daily 10,000 steps during lock-down!

[Alexey Trebukhin]

@markdd Hi! 

 

How did you manage this formula: "=VALUE(SUBSTRING('Lighting Device'.'Weight', ' ', 1))" to work? 

 

I'm struggling with text to numbers issue for "Wattage" and "weights" field since 2019! 🙂 And it seems for me that you found the solution? 

 

What am I missing? 

 

Thank you in advance! 

 

 

[markdd]

You are counting symbols and not lighting devices. Change the B column to Lighting Device Instrument Types and then the string in Column D will have the correct item to look at.

[Alexey Trebukhin]

@markdd still doesn't work for me 😞

 

Should I try anything else? 

[markdd]

Take a look at this worksheet

Worksheet.vwx

[Alexey Trebukhin]

@markdd Thank you! 

 

It works perfect! 

 

And I found my mistake! 🙂

[Lunar Waneshaft]

For anyone following this, if the default Watts entered in the OIP look like this:

 

 

add a space between the number value and the W, so it looks like this: 171 W (instead of 171W)

 

Or use a different formula... 

[Sam Jones]

@markdd Hi,

You have AutoPlot Tools.  Take a look at the "Collect and Total Weight" command.