By using this website, you agree to our Terms of Use (click here)
= iif ( ([INKitSpecStkDet.CompInventoryID] = Null), [ARTran.InventoryID] , [INKitSpecStkDet.CompInventoryID])
This is returning an integer value for the Inventory ID if true and and integer value of the Componenet Inventory ID if false - (dB sequence numbers?) . How do I get it to return the actual part numbers (not descriptions)?
You need to join ARTran and INKitSpecStkDet to IN.InventoryItem (note: the table needs to be added twice to the GI, once for each join) and then use the field [InventoryItem.InventoryCD]
@royce-lithgo
I have both of those tables linked but I will check them again. However when I use InventoryCD (which I had done initially) I get the error: Conversion failed when converting the nvarchar value 'part number ' to data type int so I tried InventoryID and I got the correct # of rows and correct data everywhere except in the iff (exp, true, false). The data from the iff isn't incorrect, it is just the int value (dB seq #?) not the part #.
Are you saying I should use InventoryCD in the iff statement or in the Relations tab or both? I get the above error in all cases.
Thanks!
In relations tab you need to relate via ID fields - in the results formula, if you want the CD values then use those in the IIf formula result.
Check that you used the right Schema field for that formula as well. If you used an int field, then you would likely get an error (similar to the one you listed above). You can leave Schema field blank in this instance.
@royce-lithgo
Thanks for the help! I got it working. I did need another join with IN.InventoryItem as I had INKitSpecStkDHdr as well as INKitSpecStkDet ...
Still not sure why I got the error messages when I used InventoryCD. I found that if I used all InventoryID I didn't get the error but it returned the int value of InventoryID and when I tried t use InventoryCD in place of just one of the InventoryID in the equation I got the error. When I replaced all of the InventoryID with InventoryCD I got the results I expected. (Schema fields were blank)
I did say that you needed to join IN.InventoryItem twice in my original post. 😉
Not quite sure what error you were getting, but your IIf statement needs to return consistent types (ie. you can't have one return an int and the other a varchar).
Online Members
