Search function for multiple tables

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.

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

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.

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 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.