Search multiple assemblies in dashboard

[srennels (Spencer)]

Hello - I am trying to re-create the functionality of a Crystal Report, replacing it with a dashboard. We have a report that basically searches by job and pulls in relevant data for a BOM checklist. I was able to recreate this as a dashboard and it works pretty well. But we have a separate report that searches by job and any number of assemblies within that job. I built the dashboard to include a search by job and assembly, but I can’t figure out how to include multiple assemblies in the search. For example, I might want to bring up the BOM checklist data for job 123-4567, but only want to view this data for assemblies 12, 16, and 25. Is there a way to search multiple of the same field (in this case, assemblies)?

In E9, the parameters for the report looked like this:

Thanks!

[gpayne (Greg Payne)]

@srennels I wrote something that would work for this, but it is a few moving pieces and not terribly elegant. In E9/10 we had a customization that wrote to to a UD table for filtering that was cleaner.

Looking forward to K21 I was able to make a bpm data form that will popup on the query and you can enter the job number and a series of assemblies comma separated. I made the BAQ updatable and on pre processing of getlist I updated the UD table with current user, job and then split the assemblies and added them to the UD table.

That data is in the UD table before the query so it can filter JobAsmbl.to give you only the assemblies you want.

As I was drinking and programming I am not posting the ugly code, but dm me if you want the example.

Greg

[srennels (Spencer)]

Thanks for the help, and apologies for my delay. I think I understand the process you’re describing, so I’ll try my hand at that and see if I run into anything. Thanks!

[srennels (Spencer)]

@gpayne Tried your suggestions and understand how the process will work, but I’m afraid I don’t have enough experience quite yet working with BPMs and BAQs to make it work with what I tried so far. I made a BPM Data Form to allow the user to enter the job number and assemblies. And then I put that in a BPM to be called. Is this correct so far? I’m not sure where to tell it to popup when using a specific dashboard? Also is there a specific way to split the assemblies to add them to the UD table? Appreciate the help!

[gpayne (Greg Payne)]

@srennels First step is to get your main BAQ to be filtered by a subquery. Add an inner sub query on JobAsmbl and hard code the job and assemblyseq you want to show in the main to test the subquery. Group that by Company, JobNum, AssemblySeq. Then after that works add a join to your UD table to get the job numbers and assemblies to filter from the data entered.

The bpm is pre-processing on the main query, so the UD data is there before the query runs.

This is the custom code block.

/* build UD24 data from bpm data form */

UD24Tableset udds = new UD24Tableset ();
Ice.Tables.UD24 UD24;

using (var txscope = IceDataContext.CreateDefaultTransactionScope())
{
  foreach (var _UD24 in Db.UD24.Where(u=> u.Key1 == Session.UserID && u.Key2 == "MyData"))
  {
    Db.UD24.Delete(_UD24);
  }

  txscope.Complete();
}



List<string> AsmSeqList = (callContextBpmData.Character01.Split(',')).Select(t => t.Trim()).ToList();

foreach (var AsmSeq in AsmSeqList)
{

  using(var UD24BO = Ice.Assemblies.ServiceRenderer.GetService<UD24SvcContract>(Db,true))
  {

    var dataRow = udds.UD24.Where(u => u.Company == Session.CompanyID &&  u.Key1 == Session.UserID && u.Key2 == "MyData" && u.Key3 == callContextBpmData.Character02 && u.Key4 == AsmSeq).FirstOrDefault();
    if(dataRow == null)
    {

      UD24BO.GetaNewUD24(ref udds);
      var aUD24 = udds.UD24.LastOrDefault();
      aUD24.Company = Session.CompanyID;
      aUD24.Key1 = Session.UserID;
      aUD24.Key2 = "MyData";
      aUD24.Key3 = callContextBpmData.Character02; //job number
      aUD24.Key4 = AsmSeq;
      aUD24.Key5 = ""; //Guid.NewGuid().ToString();



      UD24BO.Update(ref udds);

    }
    

  }
}

[srennels (Spencer)]

Thanks so much for the help - I’m getting close. I have the BPM and BAQ built, but I’m getting a reference error with the code.

Do I need to add these references somewhere? Thanks,

[gpayne (Greg Payne)]

@srennels sorry, yes you do need to add a reference for the UD table you are going to use. I used UD24, but make sure you are not using that for anything else at this time. Eventually the Key2 can be used to allow you to use the same UD table for all of these types of BAQs.

Under references click Add. The UDs are at the bottom so it will take some time before they show. you can filer by typing UD, but it will still take time.

[srennels (Spencer)]

@gpayne Ah that makes sense - I had been on that references window, but my impatience got the best of me - I should have just waited a little longer for the Ice reference to show up. Anyway, got the reference added now and there are no longer any errors. On the BAQ analyze tab, I can click Get Results and then the BPM data form pops up just like it should. I enter the job and assembly info and click OK.

This is what it returns:

It looks like it is writing the job and assembly to that UD24 table, but something isn’t getting passed correctly to JobAsmbl, as it’s pulling in lots of other jobs. I think I have an incorrect join somewhere or need criteria set, both of which I’ve tried different options for and haven’t had any success. Here is the query design:

And here is the query phrase:
/*

• Disclaimer!!!
• This is not a real query being executed, but a simplified version for general vision.
• Executing it with any other tool may produce a different result.
  */

select
[Asmfilter].[JobAsmbl_JobNum] as [JobAsmbl_JobNum],
[Asmfilter].[JobAsmbl_AssemblySeq] as [JobAsmbl_AssemblySeq],
[UD24].[Key1] as [UD24_Key1],
[UD24].[Key2] as [UD24_Key2],
[UD24].[Key3] as [UD24_Key3],
[UD24].[Key4] as [UD24_Key4],
[UD24].[Key5] as [UD24_Key5]
from Erp.JobMtl as JobMtl
inner join Erp.JobHead as JobHead on
JobMtl.Company = JobHead.Company
and JobMtl.JobNum = JobHead.JobNum
inner join (select
[JobAsmbl].[Company] as [JobAsmbl_Company],
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq]
from Erp.JobAsmbl as JobAsmbl) as Asmfilter on
Asmfilter.JobAsmbl_Company = JobMtl.Company
and Asmfilter.JobAsmbl_JobNum = JobMtl.JobNum
and Asmfilter.JobAsmbl_AssemblySeq = JobMtl.AssemblySeq
inner join Ice.UD24 as UD24 on
Asmfilter.JobAsmbl_Company = UD24.Company

It’s gotta be something simple that I’m probably missing. Any ideas? Really appreciate the help on this!

[gpayne (Greg Payne)]

@srennels The UD table is only in the sub query and I don’t see a group by in your query.

Join UD to JobAsmbl. You will have to convert AssemblySeq to character.

This is what i used for the UD criteria

[srennels (Spencer)]

@gpayne That did it - got it all working! Thank you very much for all the help!