Record Links - filter as a subform to Customer Card?

shogan@mila.ie

Hi all,

I've looked into this briefly and found that Links, say for a Customer, can not be easily implemented as a subform on the Customer Card...

I was just wondering is it built into the NAV 5 Client as opposed to being a table/form object?

Or is there a way I can treat it as a normal form?


Thanks, in appreciation!

The datalore.

einsTeIn.NET

Of course you can create a subform to the links of a certain record. The source table is Record Link. The easiest way to filter the right record links is to create a new field that is filled with the record ID when a new record is inserted.

shogan@mila.ie

Hi einstein.NET,

Thanks for your suggestion!

According to my system (NAV 5.0 SP1 Update 2), the Record ID field contains the type and no. of the record it is referencing, e.g. "Customer: 7968", "Item: 046437", etc.

How would I create a subform of the Customer Card form to say look at all records specific to the Customer No. being displayed on its card?

Not sure how to combine Type and No. in an ID field for looking up the main form...

Regards,
Stephen

rhpnt

I would suggest that you first read the ADG and then rethink your idea.

einsTeIn.NET

Indeed, that is an issue. As you might have seen in the online help it is not possible to filter on parts of a Record ID field. You can filter the whole value even though there's no hint in the online help, but anything else isn't possible.

It is possible to change the system table Record Link, thus you could store your Customer No. in a new field but I don't know if that fits for what you want to achieve.

shogan@mila.ie

Hi rhpnt,

Thanks for your comment - I am actually reading Packt Publishing's "Programming Microsoft Dynamics NAV" (Studebaker) - excellent book, albeit slightly verbose.

I have managed to create a form that filters for a specific customer via

SourceTableView: SORTING(Record ID) ORDER(Ascending) WHERE(Record ID=FILTER(Customer: 7968))

Where I am having the trouble is how I am going to dictate this form to be used as a SubFormLink, where the Record ID is actually a mash of the Customer type and Customer No.

I think I know what I want and am about 50% of getting there.

I just want the subform to show all records only for this Customer No. that I am showing on the Customer Card....

Would you know of the sections of the ADG that might help me?


Regards,
Stephen

rhpnt

It's allready implemented by design several times - e.g. in the customer card use the menu button "Customer" to select "Ledger Entries". Only entries for this customer appear.

Open the card in designer mode to see how it is implemented.

shogan@mila.ie

Ahhhh I understand what you mean now...

Yes, I usually cannibalise other forms and reports anyway.

OK let me try and deduce how I can go about doing that...

Will be back shortly...

shogan@mila.ie

OK, I don't think that I can use the SubFormLink property to have the links filtered based on the customer no., because I don't think you can filter on RecordID...

However, I did find some code that should go into the OnOpenForm() trigger of the subform, but I am not sure how to customise it to lookup the Customer No. on the main Customer Card form (http://www.mibuso.com/forum/viewtopic.php?f=23&t=34539&hilit=RecordID):

MyRecordRef.GETTABLE(MyRecord);
RecordLink.SETFILTER("Record ID",FORMAT(MyRecordRef.RECORDID));
MyRecordRef.Close;

Any more help appreciated!

einsTeIn.NET

You need to apply the No. of your current Customer Record to your subform in OnAfterGetRecord of your main form. Then use this No. to set your RecordRef to the right Record and afterwards use the RECORDID function of your RecordRef to set your Filter (like described in your example).

rhpnt

To add a subform which shows "all" records linked to the card record is a NO GO in NAV! That's why I again suggest that you read ALL developer related NAV docs (Partnersource) before attempting to modify any object.

Why is this a no go? If you noticed, no NAV card form contains a subform, because the presentation of, let's say, customer ledger entries in a customer card would have a significant impact on the system speed (reaction time) - and I'm talking only ledger entries.

Take this advice - before you start to modify anything don't ask yourself: "Can it be done?" but rather: "Should it be done?"

einsTeIn.NET

Maybe it's not a good idea to do it in a subform, but if he wants to create e.g. CommandButton solution like in the Customer Information section of the Sales Order Card then he would still don't know how to filter the Record Link table. So, the general question behind this topic is another.
I guess with Record Link table the performance won't be an issue. But if it is or will become an issue then he has to learn something about performance and NAV database design. That's the hard way. And didn't we learn it that way, too? In my opinion any solution is a good solution until it works and do not cause other issues.

shogan@mila.ie

Thanks for the input guys.

I am VERY cautious about how list (sub)forms are filtered on presentation to users (believe me when I say this, as I am a VERY EXPERIENCED Jet Reports designer too!). Performance time is a huge concern for me. I am well versed in advanced concepts of database design so I understand concepts like primary keys, foreign keys, inner, outer, left and right joins, etc.

But enough about me :P

Why I need the Record Links table filtered for a specific customer an shown on the Customer Card is because we are completely redesigning the Customer Card (as a new form) into a more dashboard-like structure. Information on a form is NOT pleasing to the eye; we're looking to structure it in such a way that Sales AND Finance dept.'s are drawn only to the sections that they need to be drawn to, and that the status of a customer can easily be determined by reducing the scan time.

To give you an example, here is what I've done to the Item Card for the Purchasing Dept. (see attached). This has gone through iterative design fine-tuning both in terms of performance and UI. Have to say as a novice designer I am quite happy with the way it turned out. Notice the tabs are removed, and fields are grouped into sensible denominations. A sheer glance and we can see how an item is performing.

For the customer card case, I intend to look at Comments and Links on a new "Sales Customer Card" - I could additionally filter for Links created in the past 6/12 months to improve performance, as we'd rarely have queries going beyond this, and even if we did, we can use standard functionality to retrieve older records in these tables for a particular customer. We don't add that many links anyway. Purely for supporting documentation only (discounts applied, queries on orders dispatched, etc.).

I agree with einstein.net: it's no fun unless you try I have been administering NAV for 9 years (this week actually) so I have a good feeling for what will work and what won't. I like to push boundaries of systems though: get bang for the serious bucks spend on it! Don't you? Sometimes things should be done, even if they tell you that they shouldn't!