Search function for multiple tables

MartinFK

Hi,

We moved our historical sales orders to a second table. (T50000 "Sales Header History" and T50001 "Sales Line History").
Don't ask why ...

To find a order the users have to try now on two different Forms (open & historical) when performing a search.

On the Form "Sales Order" I would like to implement an improved search functionality.
The user searches for a sales order by clicking into the field "No." and then Ctrl-F.

If the search fails the search should continue in T50000.
Given there is a record, I would open the relevant Form for T50000 and T50001.

Any ideas how to implement this?
I tried to catch the CurrFieldNo and use it in the OnFindRecord Trigger of the Form, but this is too late in the process.

Thanks,
Martin

carbon

If i understand to your issue, you can use something like this:
SearchCity := City;
PostCodeRec.SETCURRENTKEY("Search City");
PostCodeRec.SETFILTER("Search City",SearchCity);
FORM.RUN(FORM::"Pay Post Codes",PostCodeRec,PostCodeRec.Code)
Of course you must use different fields, filters, form.

rhpnt

Well, I have to ask: "Why didn't you use the standard archive tables?"

When the decision was made to move the data to some "special" tables, the consequences (e.g. searching) of doing so must have been considered as well!?

lvanvugt

Did you have a look at the Where-Used feature for G/L Accounts?

MartinFK

Hi all,

thanks for your answers and thanks to Luc to point out a feature I did not know yet :-)

The problem is not how to do the search and open the Form afterwards.
The issue is to make it most comfortable.
A additional button in the form or assist-edit would do, but I try for a better solution.

If the record is not found in table 36 then I would like to continue in table 50000 and if found there open the forms for 50000 and 50001.
Best if using Ctrl-F search.

Any ideas?

David_Singleton

MartinFK wrote:

Hi,

We moved our historical sales orders to a second table. (T50000 "Sales Header History" and T50001 "Sales Line History").
Don't ask why ...

OK so we can't ask why? But surely its not to late to fix it. Just move the data to the correct tables and do it properly.

rhpnt

MartinFK wrote:

The issue is to make it most comfortable.

This is THE most common argument to start the road to perdition.

MartinFK wrote:

If the record is not found in table 36 then I would like to continue in table 50000 and if found there open the forms for 50000 and 50001.

You just skeched the solution - now code it.

MartinFK wrote:

Best if using Ctrl-F search.

Certainly. Would you like it with a little cherry on the top?

MartinFK wrote:

Any ideas?

Take Mr. Singleton's advice.

Savatage

I've added a search feature that might be similar to what you are looking for.
Sometime going into Posted Invoices and filtering on orders takes a minute or two.
If you have as many Posted Invoices as us 2003-2012 and you see "Searching Records or Reading Records" message for a while.

So I wanted something instant. So I made a form called "Quick Find Orders" and it's basic function is that you enter a external doc number and it will find it if it's in either the Sales Orders or the Posted Invoices.
Now this is specific to our business need perhaps you can use something like this to search multiple tables but that's a bandaid I think in your situation.

It finds it instantly and opens the form!
You can add more setranges if needed like the cust no.
If you are looking for the "No" field - then use that as your setcurrent key!
We needed to look up external doc - that's why I used the key I did.
I will also assume you created the same or similar keys in your "History Tables"

SalesHeader -> Record -> Sales Header (Table1)
SalesInvoiceHeader -> Record -> Sales Invoice Header (Table2)
OrderNo -> Code20
SalesForm -> Form -> Sale Order
InvoiceForm -> Form -> Posted Sales Invoice

OnAfterValidate()
IF OrderNo = '' THEN BEGIN
  MESSAGE('You Cannot Search A Blank Number!');
END ELSE BEGIN
  SalesHeader.SETCURRENTKEY("Sell-to Customer No.","External Document No.");
  SalesHeader.SETRANGE("External Document No.",OrderNo);
IF SalesHeader.FIND('+')THEN BEGIN
  MESSAGE('Open Order Found');
  CLEAR(SalesForm);
  SalesForm.LOOKUPMODE(TRUE );
  SalesForm.SETTABLEVIEW(SalesHeader);
  SalesForm.RUNMODAL;
  CLEAR(OrderNo);
END ELSE BEGIN
  SalesInvoiceHeader.SETCURRENTKEY("Sell-to Customer No.","External Document No.");
  SalesInvoiceHeader.SETRANGE("External Document No.",OrderNo);
IF SalesInvoiceHeader.FIND('+')THEN BEGIN
  MESSAGE('Posted Order Found');
  CLEAR(InvoiceForm);
  InvoiceForm.LOOKUPMODE(TRUE );
  InvoiceForm.SETTABLEVIEW(SalesInvoiceHeader);
  InvoiceForm.RUNMODAL;
  CLEAR(OrderNo);
END ELSE BEGIN
  MESSAGE('Order Not Found');
  CLEAR(OrderNo);
  CurrForm.UPDATE;
  END;
 END;
END;

MartinFK

Hello Savatage,

I was thinking about the solution you have described but wanted to avoid an additional Form that needs to be opened.
The idea was to add this C/AL code at a place, where Navision ends when the search (Ctrl-F) fails.

That was the reason I asked.
And it seems that your Form solution will be the way to go.

@the others, regarding the "don't ask why"
I inherited this functionality from my successor and it not an option to change it. If I could only get rid of it ..... but this is a political decision. It is similar to the Archived Orders, but with some (important) differences. And in my opinion it is heavily redundant with the combination of Invoices and Archive. A lot of processes build on the existance of this specific tables.

Thanks for your replies.

Savatage

MartinFK wrote:

The idea was to add this C/AL code at a place, where Navision ends when the search (Ctrl-F) fails.

Moving the old invoices to a new location killed the Standard Ctrl-F. That's only going to search the table you're on.

Moving the old invoices to a new location also killed the navigate function from the customer ledger entries too
FYI:;
It doesn't have to be a new form you can add a textbox to any already made form.
A form that makes sence to why it's there.

You'll have to change some of the search criteria to fit your needs.
My form looks as below. You only need 1 Textbox to enter a number into and put the code OnAfterValidate.
When you're doing 60,000 orders a month - I'll take all the help I can get managing orders, answering questions

This solution for us was not ment to be used for your type of problem but I see where it can help you, if you don't want to move the data back to proper tables.