Using TemporaryTables within a page for use with WebServices

ScottJones

Hi,

Currently working on a large project to upgrade an existing website to use DynamicsNAV Web Services. I keep comming back to same issue again and again and wondered if anyone can answer this limitiation I have with webservices.

I have a PAGE, the page is based on a TemporaryTable (SourceTableTemporary=Yes). The purpose of the temporary table is to join two tables together and present as one list in date order to web user. The joined temporary table will contain 'Customer Calls' and 'Returns' for a specific customer.

On the event OnOpenPage I have created a function to add records from both tables to the temporary table, this all works fine BUT the filters are not applied until AFTER the temporary table has been built thus it builds temporary table for all 10000+ records prior to applying filter (Which is a far to slow!!). I have tired using the C/Side command GETFILTERS on the OnOpenPage but it only returns the filters applied at design time to the page not the ones applied through the web service.

This is my small C# test harness to apply a filter on the customer number to the page but this is not applied unitil temp table is built:

// Create instance of service and set credentials.
SvcCustomerReturnCallBuffer_Service service = new SvcCustomerReturnCallBuffer_Service();
service.UseDefaultCredentials = true;

// Create filter to return Calls & Returns History.
List<SvcCustomerReturnCallBuffer_Filter> filterArray = new List<SvcCustomerReturnCallBuffer_Filter>();
SvcCustomerReturnCallBuffer_Filter CustomerFilter = new SvcCustomerReturnCallBuffer_Filter();
CustomerFilter.Field = SvcCustomerReturnCallBuffer_Fields.Customer_No;
CustomerFilter.Criteria = "ACC1534";
filterArray.Add(CustomerFilter);

// Read data - Takes ages to return from this method as builds TemporaryTable then applys customer filter
SvcCustomerReturnCallBuffer[] results = service.ReadMultiple(filterArray.ToArray(), null,0);

//Print full list
Console.WriteLine("Full list...");
PrintList(results);

So my question is... Is it possible to pickup the filters applied though the WebService call? If so i can then apply those filters as i build up the temporary table. Globals are not persisted though separate calls so I am a little stuck!

Thank you

Scott

Egnaz

Have you tried to make a set function for the filter? To set the filter before the ReadMultiple is started.

Regards,
egnaz

ScottJones

Morning Egnaz,

I am not sure what you mean by 'set function'? The filters are definatly being passed from C# thought to the middle tier but are not being applied until after the OnPageLoad. So i cannot get the value of the filters to build the temporary table

On the DynamicsNAV Page I added an extra column with the global value, gPassedFilterValues. OnPageLoad i set gPassedFilterValues = GETFILTERS(); but no filter values are picked up at this point.

It looks as though the filters are picked up and applied after the OnPageLoad has finished

Egnaz

Hi,

have you tried to run the ReadMultiple with a "simple" filter?

I had done a small test with the vendor and I got the result I want.

Vendor_Filter vendorFilter = new Vendor_Filter();
vendorFilter.Field = Vendor_Fields.Location_Code;
vendorFilter.Criteria = "DIREKT";
Vendor[] vendors = vendorService.ReadMultiple(new Vendor_Filter[] {vendorFilter},null,0);

Regards,
egnaz

ScottJones

Just tiried above with my varables etc:

SvcCustomerReturnCallBuffer_Filter ReturnFilter = new SvcCustomerReturnCallBuffer_Filter();
            ReturnFilter.Field = SvcCustomerReturnCallBuffer_Fields.Customer_No;
            ReturnFilter.Criteria = "ACC1534";
            SvcCustomerReturnCallBuffer[] results = service.ReadMultiple(new SvcCustomerReturnCallBuffer_Filter[] { ReturnFilter }, null, 0);

and get same results, i still cannot pickup the FILTER strings OnPageLoad within web service page to apply to temporary table i build...

Lars at Microsoft thinks it maybe a know limitation/issue, but if you know/can think of a work around that would be great. I'm sure other people will want to create web services based on Temporary tables and be able to pass filters to them without the temporary table containing every customer record.

Cheers

kine

I think it have a logic: the page reads the data before the data are "read" by the webservice, thus not knowing the filter in this stage. Try to add the join as a view, use the LinkedObject to connect it into NAV as a standard table, build page over it, tryto publish it as a webservice. If it will work, I hope it will help you..

aandries

I am facing the same problem.

Is there already a solution for it? (other than creating a real table)

ScottJones

I posed the issue to Microsoft unless this is a requirement from the development community it will not be added to the development list. I've not heard any more new on it. Keep me posted if anyone knows a workaround apart from separate tables and doing business logic on ASP.NET calling side...

kine

For reading only you can use XMLPort but it will be without paging and other things...

agentnik

I ran into the same problem and found that I couldn't make it work using SourceTableTemporary. However I could make it work by turning off SourceTableTemporary and using the "old" way of using a temp table on a form, and just calling the PopulateTempTable the first time OnAfterGetRecord is called.

OnFindRecord(Which : Text[1024]) : Boolean
TempRec.COPY(Rec);
Found := TempRec.FIND(Which);
Rec := TempRec;
EXIT(Found);

OnNextRecord(Steps : Integer) : Integer
TempRec.COPY(Rec);
Found := TempRec.NEXT(Steps);
Rec := TempRec;
EXIT(Found);

OnAfterGetRecord()
IF NOT Populated THEN BEGIN
  PopulateTempTable;
  Populated := TRUE;
END;

Note this onbly works if there is at least one record in the real table - otherwise OnAfterGetRecord is never called because the NAV webservice is trying to be too "smart". I think this is the reason I couldn't make the SourceTableTemporary work.

ScottJones

Interesting... Not sure if you can apply filters from application consuming web service using this method but i might be wrong. In our case there were hundreds of thousands of records we needed to apply filters to before the records are returned as link from web server to NAV middle tier would be to slow.

It's a while ago since i look at this so apologies if cannot quite recollect )

agentnik

ScottJones wrote:

Interesting... Not sure if you can apply filters from application consuming web service using this method but i might be wrong. In our case there were hundreds of thousands of records we needed to apply filters to before the records are returned as link from web server to NAV middle tier would be to slow.

You can definitely apply filters from the application consuming the web service as that is what the filters parameter of the ReadMultiple does. i.e: .ReadMultiple(filterArray.ToArray(), null,0);

With regards to the hundreds and thousands of records, I thought this was going to be a problem with my method too (i.e. it would read the whole table, then filter after) but strangely it didn't seem to do that - as it was a lot faster than when I just called the list of all records. I can't confirm this for sure, but *maybe* this is one of those "efficiencies" that NAV WS have tried to build in.

It might (or might not) be related to the paging I used (never expected more than 3 records in my call so I page on 10) .ReadMultiple(filterArray.ToArray(), null,10);