BPM to run BAQ and fill in field

Kinetic ERP
1

I’m trying to create a BPM to run a BAQ, get a result, and paste that into a field on the QuoteHed Table. I’m not a coder, so most of what I have I pieced together from other posts on here.
When ran in isolation, it calls the BAQ, finds the correct value, and I can put it in an info message. Giving the parameter the quote number is where I’m having issues. (Currently I have it typed in for testing).

var tempQuoteDtl = (from row in ds.QuoteDtl where row.Company == Session.CompanyID && (row.RowMod == "A" || row.RowMod == "U") select row).FirstOrDefault();

  if(tempQuoteDtl != null)
  {
    int QuoteNum = Convert.ToInt32(tempQuoteDtl.QuoteNum);
    InfoMessage.Publish("test1");
              
    var tempQuoteHed = (from row in ds.QuoteHed where row.Company == Session.CompanyID && row.QuoteNum == QuoteNum && (row.RowMod == "A" || row.RowMod == "U") select row).FirstOrDefault();
        if(tempQuoteHed != null)
        {
          InfoMessage.Publish("test2");
          int QuoteNum = Convert.ToInt32(tempQuoteHed.QuoteNum);
          Ice.Contracts.DynamicQuerySvcContract tQuery = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(Db);

              if (tQuery != null)
              {
                  Ice.Tablesets.DynamicQueryTableset dsQuery = tQuery.GetByID("QuoteCost");
                  if (dsQuery != null)
                  {
                      Ice.Tablesets.QueryExecutionTableset dsBAQ = tQuery.GetQueryExecutionParameters(dsQuery); 
        
                      dsBAQ.ExecutionParameter[0].ParameterID = "QuoteNum";
                      dsBAQ.ExecutionParameter[0].IsEmpty = false;
                      dsBAQ.ExecutionParameter[0].ParameterValue = "96325";
          
                      DataSet result = tQuery.Execute(dsQuery, dsBAQ);
                      tempQuoteHed.QuoteCost_c = Convert.ToDecimal(result);
                      
                      foreach(DataRow row in result.Tables[0].Rows)
                      {
                          var quoteCost = row["Calculated_TotalCost"];
  
                          InfoMessage.Publish(Convert.ToString(tempQuoteHed.QuoteCost_c));
                      }
                      
                      dsBAQ = null;
                   }
                  dsQuery = null;
                  tQuery.Dispose();
               }
          }
  }

Any help would be appreciated!

2

What’s the issue/error you’re having with setting dsBAQ.ExecutionParameter[0].ParameterValue with the int QuoteNum value?

If it’s a type error, you could try converting the QuoteNum to a string:

instead of

dsBAQ.ExecutionParameter[0].ParameterValue = "96325";

use

dsBAQ.ExecutionParameter[0].ParameterValue = QuoteNum.ToString();
2 Likes
3

Good morning!
I made this change, but the BPM doesn’t seem to even run (this is not due to the change made, it never ran before). I have a line:
InfoMessage.Publish(“test1”);
This does not appear when testing.
This line must be returning as null, but I don’t know why

var tempQuoteDtl = (from row in ds.QuoteDtl where row.Company == Session.CompanyID && (row.RowMod == "A" || row.RowMod == "U") select row).FirstOrDefault();
  if(tempQuoteDtl != null)
  {
4

Those are the wrong characters for code posts. You want backticks. ```

They are under the tilde ~.

1 Like
5

Assuming that this is a method directive - (1) what method are you running this on and (2) what actions are you taking in the UI that you are expecting this BPM to run?

6

Hello, Eric

This is on the Quote Update method. I’m testing it by writing messages with key info throughout the code, so I know where it fails. I go to a quote, change a PO number and save to proc the update method.
This is the current state of the code:

  var tempQuoteHed = (from row in ds.QuoteHed where row.Company == Session.CompanyID /*&& (row.RowMod == "A" || row.RowMod == "U")*/ select row).FirstOrDefault();
  if(tempQuoteHed != null)
  {
    int QuoteNum = Convert.ToInt32(tempQuoteHed.QuoteNum);
    InfoMessage.Publish(Convert.ToString(QuoteNum));
              
         Ice.Contracts.DynamicQuerySvcContract tQuery = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(Db);

              if (tQuery != null)
              {
                  Ice.Tablesets.DynamicQueryTableset dsQuery = tQuery.GetByID("QuoteCost");
                  if (dsQuery != null)
                  {
                      Ice.Tablesets.QueryExecutionTableset dsBAQ = tQuery.GetQueryExecutionParameters(dsQuery); 
        
                      dsBAQ.ExecutionParameter[0].ParameterID = "QuoteNum";
                      dsBAQ.ExecutionParameter[0].IsEmpty = false;
                      dsBAQ.ExecutionParameter[0].ParameterValue = QuoteNum.ToString();
          
                      DataSet result = tQuery.Execute(dsQuery, dsBAQ);
                      //ttRecord["QuoteCost_c"] = Convert.ToDecimal(result);
                     
                     foreach(DataRow row in result.Tables[0].Rows)
                      {
                          var quoteCost = row["Calculated_TotalCost"];
  
                          InfoMessage.Publish(Convert.ToString(quoteCost));
                          
                          tempQuoteHed.QuoteCost_c = quoteCost; //ISSUES HERE
                      }
                      
                      dsBAQ = null;
                   }
                  dsQuery = null;
                  tQuery.Dispose();
               }
          }

I’m having issues with setting the results of the BAQ into a field on the QuoteHed table, QuoteCost_c.
Error message: ‘QuoteHedRow’ does not contain a definition for ‘QuoteCost_c’ and no accessible extension method ‘QuoteCost_c’ accepting a first argument of type ‘QuoteHedRow’ could be found (are you missing a using directive or an assembly reference?)

Upon checking the QuoteHed table, QuoteCost_c is definitely there, and the tables are sync’d via DMR.

Dave

7

did you try it like this tempQuoteHed["QuoteCost_c"]?

1 Like
8

Nice! You’re getting much closer. There’s a slightly different syntax for setting UD fields.

Try this instead.

tempQuoteHed["QuoteCost_c"] = quoteCost;
1 Like
9

I have tried this!
I am getting the correct number, and no errors. When I test, everything indicates that it works fine, but a BAQ still shows the value as 0.
I am coding all this in a Post-Process update
Dave

10

Maybe you need to use the .SetUDField syntax?

tempQuoteHed.SetUDField<Type>("fieldname", "value");

Where Type is System.String, System.DateTime, etc.

— scratch this below stuff, because after reading more closely, it’s not a BAQ row you are editing. above should work

Well, no, that wouldn’t work here since it’s a BAQ result.

I think you just need to add the column formally before setting it?

result.Tables[0].Columns.Add(Ice.IceColumn.Create("name", typeof(string)));

1 Like
11

Try recreating this as a pre-processing method and run it again. If you assign the value to that UD field, it should save the data properly.

1 Like
12

are you trying to get the value that is being just set in the method? maybe you are trying to get data before transaction is complete yet and they are not avaialble

1 Like
13

I’ve changed it to a pre-process and tested. I also added the field to the header for better clarification.
It’s still popping up messages with the correct data, pulled from the BAQ. However, it’s still not saving. I even changed the field itself (QuoteCost_c) to some arbitrary number and saved to proc the update method. It went back to 0.00.

14

In PreProcess you need to select the row with row.RowMod == "U"

1 Like
15

Thank you!
This was the final thing I was missing. Didn’t have it in the original var.
Thank you to everyone who helped!!!

2 Likes