This is a simple task and I know there is a simple solution, but I just can't get this right.
I have a table, Chemicals, with a list of chemicals and properties, one of which is Density. In another table, Lots, I have the lot numbers and quantities of each lot. Each chemical can have many lots, so Chemical is a picklist in Lots. I need to use the density information for the Chemical in an equation in the Lot table. I have tried the below formula and multiple variation of it, but I can't get it to work.
I did try that exact method. Unfortunately, it returns the correct values only if there is only one Lot per Chemical. When there are multiple lots per chemical, this does not work.
Hi @JCoop thanks for the screenshot! That helps. However, I can't quite tell what the selection was for cell B6 was before you started editing (it seems to be in editing state). If it was the chemical that starts with Decaflu..., then the results for every row seem correct to me. The number of lots should not affect the formula above as it's merely getting the value of the [Density (g/ml)] column from the linked row in [Chemical] column. The only place where this will not work correctly is if column [Chemical] does not contain a rowlink. That case can be fixed by having a formula like =IFERROR([Chemical][Density (g/ml)], "N/A"), however I suspect that's not what you're looking for. An alternative you could try is =FINDROW(Chemicals,"Chemicals[Chemical]=[Chemical]")[Density (g/ml)].
Column B is formatted with a Rowlink to he Chemicals table. B6 is not in an editing state, it is selected to show the values in the Chemicals table, specifically the Density value. I highlighted the appropriate cells to demonstrate the issue I am seeing. Using the formula you suggested in column K, =[Chemical][Density (g/ml)], the value for the density in the Chemicals table (viewable in the expanded view from the selection of B6), highlighted yellow, is 1.49 for dichlorodifluoromethane. In the Lots table, there are 2 entries for dichlordifluoromethane on row 5 (green) and row 6 (blue). The first row, 5, of the Lots table has the correct value, 1.49. The second row, 6, of the Lots table has the incorrect value, 1.517.
I also tried the second formula you suggested, =FINDROW(Chemicals,"Chemicals[Chemical]=[Chemical]")[Density (g/ml)], in cell K8 (not in the column format), but it returns an error.
It looks like you may be running into a bug here, @JCoop. Would you mind sending an issue report following instructions under Reporting Bugs & Issues in Honeycode community guidelines?
In the meantime, can you also try the following to see if that works for you?
