datasheet subrow math for light devices

[gtech]

Hi all,

I’m learning to use Vw spotlight and I would like to create, in a datasheet, a column that divides the number of objects (COUNT) and a number contained in a user field.

If I insert this function "=INT(COUNT)/VALUE('Lighting Device'. 'User Field 1')" in the line I get the right result, but if I try to round up the result "= ROUNDUP (INT (COUNT) / VALUE ('Lighting Device'. 'User Field 1'), 0)" the function doesn't work, I also tried alternative rounding methods but without success.

I tried to write some scripts, but I can't find sufficient examples or explanations, what I need is to read the quantity of objects (same light device) and divide them using the number in user field 1, and then return the result in the subrow corresponding in datasheet.

I wrote a script that counts the subrows, reads the contents of each subrow with the COUNT, divides it by the number of the subrow corresponding to the user field, rounds it and gives me the result as a popup, I have not found a function that allows me to write in a subrow.

 

can anyone help me? maybe referring me to the resources where I can understand how to do it.

[michaelk]

It's working for me.

 

I don't think you need the INT function on the count.  The count can't help it - it's always an integer.

 

Can you post a file with a few lighting devices and a worksheet?

 

What version of VW are you using?

 

 

 

[gtech]

47 minutes ago, michaelk said:

It's working for me.

 

I don't think you need the INT function on the count.  The count can't help it - it's always an integer.

 

Can you post a file with a few lighting devices and a worksheet?

 

What version of VW are you using?

 

 

 

Hi,

you're right, I don't need the INT function.

as you can see in the picture on F column, if I add "roundup" funcion, as result I have the COUNT

in the second screenshot i tried to execute the functions separately, but the result of roundup is "quantity". To explain myself better I also copied the functions in the first line. In green color is what I would like to have, with excel it works.

VW 2021 pc.

Thanks!

Edited May 5, 2021 by gtech

[Pat Stanford]

I read this very quickly, but there is a serious limitation to SUMmarized subrows.  

 

Where we normally want to use the displayed values, VW does not do so and used the results of each subrow individually.

 

I have a feeling that there is a long drawn out workaround involving extensive Worksheet scripts to decrypt the formulas of the database, gather the data independently and then do the calculation, I have not made the time to work it out.

 

If I am answering the wrong question, I apologize.

[michaelk]

It's hard to tell without knowing what the formula for the E column in the database is and the formulas in the non database cells are.

 

There's usually a way to make it work, but I'm not totally clear on what you want the worksheet to do 🙂 

[gtech]

2 hours ago, michaelk said:

It's hard to tell without knowing what the formula for the E column in the database is and the formulas in the non database cells are.

 

There's usually a way to make it work, but I'm not totally clear on what you want the worksheet to do 🙂 

in E column=VALUE('Lighting Device'.'User Field 1'), but in the F formula, instead of reading the values from other cells, I take them directly, and so the E column is used to remind me the numbers in the user field.

 

Knowing the number of fixtures used, knowing how many fixtures each box (Flycase) contains, I want to know how many boxes for each type of fixture I have to take from the warehouse.

 

[gtech]

8 hours ago, Pat Stanford said:

I read this very quickly, but there is a serious limitation to SUMmarized subrows.  

 

Where we normally want to use the displayed values, VW does not do so and used the results of each subrow individually.

 

I have a feeling that there is a long drawn out workaround involving extensive Worksheet scripts to decrypt the formulas of the database, gather the data independently and then do the calculation, I have not made the time to work it out.

 

If I am answering the wrong question, I apologize.

 Hi pat,

I wrote some scripts, also based on your examples, one is disconnected from the worksheet, where it asks me with the message box to enter the data and then there is a pop up with the result.

another one works with the subrows of the worksheet, reads the content and does the math and for each subrow, a popup returns with the result, but I have not found a function that writes the result in the subrows.

Subrow Case.txt

sorry but i'm not as clean as you are in scripting in the experimental phase. 😅

[Pat Stanford]

I am not at all clean in the script test phase (except for indentation. I can't live with too little indentation). I just take the time to clean up anything more than a couple of lines before I post as I know someone will ask about them 5 years from now and I would rather have the comments.  ;-)