Is the Item Quantities By Warehouse BIE view not an option?
ALEs typically can only be linked to other tables where there is a one to one relationship between tables, they don't have any built in functions to sum a value across multiple records in a one to many scenario like CI_Item and IM_ItemWarehouse creates.
If you only need a one to one link, you can set up a calculated column and use function built into the ALE program called FNRead for numeric values and FNRead$ for string values. Here is an example. The first argument is the lookup value, i.e. the record in the target table will be selected if the first argument matches a key using the primary index in the target table. The second argument is the target table without the extension. I never quite figured out what the third and fourth arguments mean but after trial and error, what you see below works. The fifth argument is the target field, you do not need the trailing dollar sign for string fields in the fifth argument but you do need the trailing dollar sign on the function name for string fields.
FNREAD$(STR(ExtendedDescriptionKey), "CI_ExtendedDescription", "1", 0, "ExtendedDescriptionText")
Here is an example of taking the above example a step further so the result is a single column that will contain the full description. This uses the TBL function and the boolean evaluation on the ExtendedDescriptionKey being greater than "0000000000" or not. If it isn't, the TBL function returns the second argument, in this case, the standard description, if it is, the TBL function returns the extended description.
TBL(STR(ExtendedDescriptionKey)>"0000000000", STR(ItemCodeDesc), FNREAD$(STR(ExtendedDescriptionKey), "CI_ExtendedDescription", "1", 0, "ExtendedDescriptionText"))
Now neither of these solve your problem since you need to sum the value of many records in IM_ItemWarehouse, to achieve this, you have to leverage a function called XEQ and a text file contain ProvideX code to return the value you are after. XEQ will do an in-line CALL to a program which is specified in the first argument and return the value in the variable passed as the second argument. Every argument after the second is dependent on the program being called.
Here is an example of using XEQ in a calculated column and passing the arguments that allow this to easily be recycled for other purposes. I could have hardcoded a lot of stuff so the only argument would be the variable used to return the value but i figured i'd do it this way so it can be used for other things as well.
XEQ("..\CM\Script\ALE_IM_QtyOnSO.pl", nReturnedFieldData, nReturnedFieldData, (%C$), "", "IM_ItemWarehouse", "kPrimary", (dat.ItemCode$), "QuantityOnSalesOrder")
Here is the target program. This version uses a file channel which is opened once and stored into the session's script object as a variable that is retrieved each time so it isn't constantly opening the file using a new channel.
! XEQ("..\CM\Script\ALE_IM_QtyOnSO.pl", nReturnedFieldData, nReturnedFieldData, %C$, "", "IM_ItemWarehouse", "kPrimary", (dat.ItemCode$), "QuantityOnSalesOrder")
SETERR ERROR_HANDLER
ENTER nReturnedFieldData, (sCompany$), (sModule$), (sTable$), (sIndex$), (sKey$), (sField$)
nReturnedFieldData = 0
IF sCompany$ = "" THEN sCompany$ = %C$
IF sModule$ = "" THEN sModule$ = MID(sTable$, 1 , 2)
IF sTable$ = "" THEN GOTO EXIT_HANDLER
IF POS(UCS(".M4T") = UCS(sTable$)) = 0 THEN sTable$ = sTable$ + ".M4T"
sTable$ = "..\MAS_" + sCompany$ + "\" + sModule$ + sCompany$ + "\" + sTable$
IF sIndex$ = "" THEN sIndex$ = "kPrimary"
IF sKey$ = "" THEN GOTO EXIT_HANDLER
IF sField$ = "" THEN GOTO EXIT_HANDLER
%SYS_SS'ScriptObject'GetStorageVar("nTable_Chn", nTable_Chn)
IF nTable_Chn <= 0 THEN {
OPEN(HFN, IOL = *, ERR=ERROR_HANDLER)sTable$
nTable_Chn = LFO
%SYS_SS'ScriptObject'SetStorageVar("nTable_Chn", nTable_Chn)
}
IF nTable_Chn > 0 THEN {
SELECT * FROM nTable_Chn, KNO = sIndex$ BEGIN sKey$ + $00$ END sKey$ + $FF$, ERR = *CONTINUE
nReturnedFieldData = nReturnedFieldData + QuantityOnSalesOrder
NEXT RECORD
}
GOTO EXIT_HANDLER
ERROR_HANDLER:
GOTO EXIT_HANDLER
EXIT_HANDLER:
EXIT
Here is another version that does not use file channels and just reads from the file. I honestly don't know which method is better.
! XEQ("..\CM\Script\ALE_IM_QtyOnSO.pl", nReturnedFieldData, nReturnedFieldData, %C$, "", "IM_ItemWarehouse", "kPrimary", (dat.ItemCode$), "QuantityOnSalesOrder")
SETERR ERROR_HANDLER
ENTER nReturnedFieldData, (sCompany$), (sModule$), (sTable$), (sIndex$), (sKey$), (sField$)
nReturnedFieldData = 0
IF sCompany$ = "" THEN sCompany$ = %C$
IF sModule$ = "" THEN sModule$ = MID(sTable$, 1 , 2)
IF sTable$ = "" THEN GOTO EXIT_HANDLER
IF POS(UCS(".M4T") = UCS(sTable$)) = 0 THEN sTable$ = sTable$ + ".M4T"
sTable$ = "..\MAS_" + sCompany$ + "\" + sModule$ + sCompany$ + "\" + sTable$
IF sIndex$ = "" THEN sIndex$ = "kPrimary"
IF sKey$ = "" THEN GOTO EXIT_HANDLER
IF sField$ = "" THEN GOTO EXIT_HANDLER
SELECT * FROM sTable$, KNO = sIndex$ BEGIN sKey$ + $00$ END sKey$ + $FF$, ERR = *CONTINUE
nReturnedFieldData = nReturnedFieldData + QuantityOnSalesOrder
NEXT RECORD
GOTO EXIT_HANDLER
ERROR_HANDLER:
GOTO EXIT_HANDLER
EXIT_HANDLER:
EXIT
The first line of both versions have what's required to be entered in the ALE column calculation for easy reference.
Here's a sample of it in action.
------------------------------
David Speck II
Tennessee Software Solutions
------------------------------