x Want to participate? Join the group to interact
Options
  • State Verified Answer
  • +1 person also asked this people also asked this
  • Replies 16 replies
  • Answers 1 answer
  • Subscribers 87 subscribers
  • Views 3816 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
91
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
962
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
thetechguy1

Import into the BM_ComponentReportSelection.M4T!!!!

SOLVED
Posted By

Is there any way to populate the BM_ComponentReportSelection.M4T  without manually keying it in the Gross Requirements Report?   It is not an available table to import into for Bill of Materials. https://solitaire.onl/ https://9apps.ooo/ https://bluestacks.vip/

Parents
  • +1
    verified answer

    You could use a script to get a handle to the BM_ReportBillSelection_bus object and write the records into the table.

    The primary key consists of the BillNo+Revision+BillOption1+BillOption2+BillOption3+BillOption4+BillOption5+BillOption6+BillO
    ption7+BillOption8+BillOption9.

    You can either use SetKey with every part of the key (except the last part) padded to the full length of the field or use SetKeyValue for each field individually followed by a SetKey.

    Then use SetValue on the other fields in the table like QuantityRequired, BillFormat, ComponentWarehouseCode, etc.

    Finally use Write to write the record to the table.

    This link has more details on the rest of the fields in the table.

    http://help-sage100.na.sage.com/2019/FLOR/#File_Layouts/Bill_of_Materials/BM_ComponentReportSelection.htm

  • 0 in reply to David Speck

      I am wondering if you ever got this to work. After a successful login I tried the following snippet which is not working. Everything up to the first SetKeyValue works. All the SetKeyValue's fail (retVal=0) then the SetKey returns a null (retVal = ).

    Set oScript = CreateObject("ProvideX.Script"
oScript.Init(MASInitPath)
Set oSS = oScript.NewObject("SY_Session")

retVal = oSS.nSetDate("B/M",ModuleDate)
retVal = oSS.nSetModule("B/M")

retVal = oSS.nSetProgram(oSS.nLookupTask("BM_ComponentReqrmntReport_ui"))
Set oBusCompReqRpt = oScript.NewObject("BM_ReportBillSelection_bus",oSS)

retVal = oBusCompReqRpt.nSetKeyValue("BillNo$", sBillNo) 
retVal = oBusCompReqRpt.nSetKeyValue("Revision$", "000")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption1$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption2$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption3$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption4$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption5$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption6$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption7$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption8$", "")
retVal = oBusCompReqRpt.nSetKey()

  • 0 in reply to connex

    Odd, i just stepped through this using the command window with the report task open, and was able to set the key value on every field and SetKey returned 2.

    I wonder if when getting a handle to BM_ReportBillSelection_bus, it expects another parameter to identify the report type since there are three reports that share the selection table.

  • 0 in reply to David Speck

    Does setting the program to the lookup task for "BM_ComponentReqrmntReport_ui" establish the report? Also, I am unable to to a nMoveFirst, nGetKey, nClear or nAddline on the object after setting it with NewObject("BM_ComponentReqrmntReport_ui",oSS). I guess there could be another parameter, but I don't have the source, only the object reference.

  • 0 in reply to connex

    That sets the security object but i'm wondering if the NewObject/GetObject need additional parameters the way the CM_UDT_Svc and CM_UDTMaint_Bus classes need the additional parameter to specify the UDT name.

  • 0 in reply to David Speck

    Well i think i'm on to something but also don't have source.

    Using this snippet, GetTable returns "*SKIP*".

    Yet using GetTable from the command window of the report yields BM_ComponentReportSelection.

    i'm going to test what happens if the new object is actually the RPT class and then try to get the handle to the selection bus object from that.

  • 0 in reply to David Speck

    Got it figured out. You need the following added between after setting the object and before calling any table related methods, like SetKeyValue, MoveFirst, etc. You do not need any additional arguments in the NewObject/GetObject and you do not have to use the _rpt class, i tested this using the _bus class.

    oBusCompReqRpt.nSetSelectionInfo "BM_ComponentReportSelection"

  • 0 in reply to David Speck

    Adding that statement was like the dam breaking and most everything started working. I was able to add lines with BeillNo's. Thank you David! Very much appreciated. Any idea why a SetValue to QuantityRequired would return a 1, but not change that field? (I verified it was invoked with values other than the default 1)

    retVal = oBusCompReqRpt.nSetValue("QuantityRequired", nQuantityRequired)

  • 0 in reply to connex

    Not sure off the bat, i didn't test any further beyond verifying the SetKey succeeded. I don't see any typos so only thing obvious to ask it that are you sure your variable contains a valid numeric value greater than 0?

    Can you clear sLastErrorMsg prior, then check it after setting the quantity required?

  • 0 in reply to David Speck

    Here is a snippet of what i used to test the other fields.

    EDIT: This sample code is not for production, it can be adapted for production but it contains very aggressive outputting of the LastErrorMsg property after each method to make sure it works.

    sMessage = ""
Set oBM_ReportBillSelection_Bus = oSession.oGetObject("BM_ReportBillSelection_Bus")
nRetVal = 0 : nRetVal = oBM_ReportBillSelection_Bus.nSetSelectionInfo("BM_ComponentReportSelection")
sMessage = sMessage & "oBM_ReportBillSelection_Bus.nSetSelectionInfo: " & nRetVal & vbCrLf
If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & "oBM_ReportBillSelection_Bus.sLastErrorMsg: " & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
oBM_ReportBillSelection_Bus.nSetKeyValue "BillNo$", "CABIN-01000-IH"
If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
oBM_ReportBillSelection_Bus.nSetKeyValue "Revision$", "000"
If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
oBM_ReportBillSelection_Bus.nSetKeyValue "BillOption1$", ""
If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
oBM_ReportBillSelection_Bus.nSetKeyValue "BillOption2$", ""
If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
oBM_ReportBillSelection_Bus.nSetKeyValue "BillOption3$", ""
If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
oBM_ReportBillSelection_Bus.nSetKeyValue "BillOption4$", ""
If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
oBM_ReportBillSelection_Bus.nSetKeyValue "BillOption5$", ""
If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
oBM_ReportBillSelection_Bus.nSetKeyValue "BillOption6$", ""
If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
oBM_ReportBillSelection_Bus.nSetKeyValue "BillOption7$", ""
If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
oBM_ReportBillSelection_Bus.nSetKeyValue "BillOption8$", ""
If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
oBM_ReportBillSelection_Bus.nSetKeyValue "BillOption9$", ""
If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
nRetVal = 0 : nRetVal = oBM_ReportBillSelection_Bus.nSetKey()
sMessage = sMessage & "oBM_ReportBillSelection_Bus.nSetKey: " & nRetVal & vbCrLf
If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
If nRetVal <> 0 Then
	oBM_ReportBillSelection_Bus.nSetValue "BillFormat$", "1"
	If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
	oBM_ReportBillSelection_Bus.nSetValue "EffectiveDate$", oSession.sModuleDate
	If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
	oBM_ReportBillSelection_Bus.nSetValue "QuantityRequired", 6
	If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
	nRetVal = 0 : nRetVal = oBM_ReportBillSelection_Bus.nWrite()
	sMessage = sMessage & "oBM_ReportBillSelection_Bus.nWrite: " & nRetVal & vbCrLf
	If oBM_ReportBillSelection_Bus.sLastErrorMsg <> "" Then sMessage = sMessage & oBM_ReportBillSelection_Bus.sLastErrorMsg & vbCrLf : oBM_ReportBillSelection_Bus.sLastErrorMsg = ""
End If
Set oBM_ReportBillSelection_Bus = Nothing
If sMessage <> "" Then MsgBox sMessage

    Here is the result.

    EDIT:

    If you still have issues, may need to see if you have any third party add-ons that might be interfering.

  • 0 in reply to David Speck
    SUGGESTED

    Thanks again David. I had forgotten to convert the quantity from a CSV to a number. It seems odd that it SetValue would return 'successful' if it was unable to store the text into a number field. Here's the essential working snippet:

    retVal = oSS.nSetModule("B/M")
retVal = oSS.nSetDate("B/M",ModuleDate)
		
'Handle security and the business object
retVal = oSS.nSetProgram(oSS.nLookupTask("BM_ComponentReqrmntReport_ui"))
Set oBusCompReqRpt = oScript.NewObject("BM_ReportBillSelection_bus",oSS)
		
'Identify the selection grid
retVal = oBusCompReqRpt.nSetSelectionInfo("BM_ComponentReportSelection")
retVal = oBusCompReqRpt.nDeleteAllRows()

'Create a new entry
retVal = oBusCompReqRpt.nSetKeyValue("BillNo$", sBillNo) 
retVal = oBusCompReqRpt.nSetKeyValue("Revision$", "000")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption1$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption2$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption3$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption4$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption5$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption6$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption7$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption8$", "")
retVal = oBusCompReqRpt.nSetKey()

'Loop for each Bill
retVal = oBusCompReqRpt.nSetValue("BillFormat$", "1")
retVal = oBusCompReqRpt.nSetValue("EffectiveDate$", ModuleDate)
retVal = oBusCompReqRpt.nSetValue("ComponentWarehouseCode$", "000")		
retVal = oBusCompReqRpt.nSetValue("QuantityRequired", nQuantityRequired)
retVal = oBusCompReqRpt.nWrite()
		
oBusCompReqRpt.DropObject()
Set oBusCompReqRpt = Nothing

Reply
  • 0 in reply to David Speck
    SUGGESTED

    Thanks again David. I had forgotten to convert the quantity from a CSV to a number. It seems odd that it SetValue would return 'successful' if it was unable to store the text into a number field. Here's the essential working snippet:

    retVal = oSS.nSetModule("B/M")
retVal = oSS.nSetDate("B/M",ModuleDate)
		
'Handle security and the business object
retVal = oSS.nSetProgram(oSS.nLookupTask("BM_ComponentReqrmntReport_ui"))
Set oBusCompReqRpt = oScript.NewObject("BM_ReportBillSelection_bus",oSS)
		
'Identify the selection grid
retVal = oBusCompReqRpt.nSetSelectionInfo("BM_ComponentReportSelection")
retVal = oBusCompReqRpt.nDeleteAllRows()

'Create a new entry
retVal = oBusCompReqRpt.nSetKeyValue("BillNo$", sBillNo) 
retVal = oBusCompReqRpt.nSetKeyValue("Revision$", "000")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption1$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption2$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption3$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption4$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption5$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption6$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption7$", "")
retVal = oBusCompReqRpt.nSetKeyValue("BillOption8$", "")
retVal = oBusCompReqRpt.nSetKey()

'Loop for each Bill
retVal = oBusCompReqRpt.nSetValue("BillFormat$", "1")
retVal = oBusCompReqRpt.nSetValue("EffectiveDate$", ModuleDate)
retVal = oBusCompReqRpt.nSetValue("ComponentWarehouseCode$", "000")		
retVal = oBusCompReqRpt.nSetValue("QuantityRequired", nQuantityRequired)
retVal = oBusCompReqRpt.nWrite()
		
oBusCompReqRpt.DropObject()
Set oBusCompReqRpt = Nothing

Children

*Community Hub is the new name for Sage City