I’ve been playing around with Planetary Industry and have made a neat spreadsheet for easy overview. I’ve even looked into making some LAMBDA functions of my own but I’ve noticed something odd. Using this formula
with B9 containing the object for one of my PI planets. What the formula does is returning the Facility that has any P1 Products in its Storage.
The LAMBDA function I’ve created takes in a Facility reference (the result from above formula) and uses that to calculate the total volume at the Facility. Named Reference is as follows: LAMBDA(Facility;Facility.contents.amount*Facility.contents.type.volume) which I’ve appropriately named EVEONLINE.PLANET.STORAGE.VOLUME.
What I cannot figure out is why there is a difference in the results of my EVEONLINE.PLANET.STORAGE.VOLUME (LAMBDA) function when I input the formula I listed at the top, and when I input a cell-reference. All I’ve been able to figure out is that, whether through LAMBDA function or not, the calculation is correct when referencing a cell with the Facility-object and incorrect when input is a formula - seems to just use the first row of the array(s).
Anyone able to shed some light on why this is the case? or How I might be able to fix this?
@Hundasuupa Perhaps you could investigate why there seems to be a difference between using a cell-reference to an object and inputting a formula for same object?
I have found when I cram everything into one cell I get one result, and when I set up a sequence of steps I get another. Not being a coder in any fashion, I put this down to me.
Now, ‘IF’… I understand what you are trying to do, this is what I have been trying to get from my colonies, is an updated amount in the Silos/Launch Pads and add those to existing amounts in POCOs and station inventories. So I can plan my PI session efforts.
Next, would be to add the amounts in outstanding courier contracts.
The biggest problem has been the update rate from the server.
i will try to replicate it. But, one thing to consider is the idiosyncrasies of the PI endpoint. Did you by any chance have a character logged in checking the contents of the planets PI storage while you were comparing the values returned?
The PR endpoint only updates when the planet is actually checked in game. PI storage quantities are calculated dynamically when they are accessed in game. They are not static values. One possible explanation is that you checked the contents of the PI storage between formula runs and the values updated, which made it appear that the formulas were producing different results, when it fact the ESI had simply sent different values.
If you have not already ruled this out, you might check to see if the discrepancy persists when no character is logged in and accessing PI planets in game.
You’re half right, I’m only trying to calculate the Fill Level of planets’ storage levels (Launchpad or Storage Facility) to determing whether I need to perform Expedited Transfer to the Launchpad or Export to POCO.
I only really have noted one thing down about Lambda function, a bug issue from the intial release.
”MAP(LAMBDA” doesn’t work with EVEONLINE.X functions.
Most of the interactions with the customfunctions can be complicated since they are async fired, and often racing against each other, (global scope tokens for example break down so hard).
They often behave fine with something straight forward in a waterfall reference path. If it is referenced in some other way it might not be taking in the expected outputs, that’s all that really comes to mind. Wish I could be more helpful!
My LAMBDA custom function isn’t the issue. When I compact the formula used (see image in prev. post) in table “Cell reference” into a single cell formula (does not make use of my LAMBDA function) it seems to only use the first row of the facility’s array object. Whether this is a defect of Excel or Add-in I do not know.
Could very well be that the SUMPRODUCT() function does not play nice with arrays unless they are a range on a worksheet.
Gotcha, I’ve read the post a bit better. Think I’m better grasping it now.
Formulas probably aren’t detected in a way that is compatible to the specific function taking it in. Since the A1.field_that_is_a_list works. It’s probably likely that a FILTER(x,x) wrapper around could trick it into working, even if it a non filtering filter. Maybe?
The cause is likely sort of a pre-render check in sumproduct calc on custom functions, which I’d guess is some downside/quirk they decided was quirky enough to not fix.
