Well, hello again. If you just got started with Tabular Editor scrips and programming in general, maybe today’s topic might be a bit too much, but if you either familiar with other programming or already know your way in c# scripts, then this will certainly become a valuable thing: in-script classes! I know it sounds scary and not something that you need, but if you want write awesome code and make your coding life better in general, they you need to pay attention: In-Script classes for Tabular Editor C# Scripts are a thing.

In part 1 of this article I already mentioned that you should start with this blog post. Today I say that if you want to go forward with this you MUST start with that blog post, period. Even if you are lucky enough to be using Tabular Editor 3, I strongly recommend the Visual Studio approach to get the intellisense in the functions methods that we’ll create. Yes we’ll create a repository of reusable code that will make the main procedure much leaner and at the same time much more powerful and robust. The reason is that we’ll encapsulate all the little validations, error messages and the like, so we don’t have to take care of that in the main script.

Daniel Otykier did a great job making many operations easier to do with his TOMWrapper. At the same time, though we can always wish for some extra functionality. What about if AddMeasure had some parameters to say for instance «if there’s a measure with the same name (or same annotation!) do not create any measure» ? In my Excel days I had plenty of VBA code to do common stuff — createWorksheetIfNecessary was one of them. That way you don’t have to do all the checks in the main procedure. You encapsulate it and put it at the end of the script. The best part? we’ll get intellisense of these new functions too when we use then inside Visual Studio, so we can add a bunch of optional parameters with their default values and all.

Open your Visual Studio «Solution»

Yes, we’ll add another project to the solution . We created for our coding environment. Yes, a solution can include several projects. Right-click on solution -> Add -> Add Project

Now we need to select the exact same thing we did for our coding environment project,  so «Class Library (.Net Framework)» is the way to go

Provide a name and make sure to select framework 4.7.2

If it sounds familiar is because these are the exact same steps we did for the original coding environment project. And actually we’ll continue adding the references and so on

Add references to TOMWrapper.dll and TabularEditor.exe

Add a reference to TOMWrapper14.dll in this folder

%APPDATA%\..\Local\TabularEditor

And TabularEditor.exe in this folder

%programfiles(x86)%\Tabular Editor

If you want the step by step part go check again the original article. If you just built the coding environment project you might still have the references listed under browse, as recent references.

Then at the top of the file add the same two lines as usual

using TabularEditor.TOMWrapper;
using TabularEditor.Scripting;

If you understand the caveats explained in the original article, feel free to add

using static TabularEditor.Scripting.ScriptHelper;

Let’s create a static class

Ok, now we got some new foundations for our class. And yes, here we’ll break quite a few of the rules of a good csharper, but that’s for a good reason. We want to have the easiest experience when putting together the script and the class, ideally with no extra steps, since we’ll have to do it quite a few times and that’s already a pain.

First thing we’ll put all of it in a single class, and we’ll use a short name to make things easier when writing our script.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TabularEditor.TOMWrapper;
using TabularEditor.Scripting;


namespace GeneralFunctions
{
    public static class Fx
    {
          //here I'll put my methods      
    }
}

Now, we need to find a piece of code we want to reuse over and over. Since we can reuse it also within our class, it can be quite small. Something I often do is start with already existing functions and wrap some code around it to have more options. Creating stuff only if it’s not yet present (and return the reference to the thing in any case) is one big use case. How you determine if something is present can be done in different ways, by name, by annotation, by their properties…

We’ll try with a «CreateTable» method as follows:

So, let’s go through some of this. First notice the «static» before almost everything. That means that there’s only one of it, you can’t make «instances of it». Most documentation on classes is about defining cars with attributes and so on, but that’s not what we’re doing here. Now you see that when I added the Model variable, Visual Studio asked me to add also a «static readonly» before it as well. So far so good. And finally we get to the method. In the method we start with public, so that we can call it from outside the class (that’s the whole point!), then we also need to put «static» and the third word is the type that will return the method. In this case the method will return a Table type object. Now we are on the fourth word, that’s the method name! Start with uppercase, or you’ll get a warning from Visual Studio. And after the method name, between brackets, we start specifying the arguments. First data type, then argument name, and then default value, if any (that makes it an optional parameter). If you have optional and non-optional parameters, start with the non-optional ones. After the parameters, open curly brackets and off we go!

But wait,  why do I have that red underline on the method name??

C’mon! I have not even started yet to type the code! This is what sometimes I’d like to say to Visual Studio

Oh well, let’s type some code then.

public static class Fx
    {
        static readonly Model Model;

        public static Table CreateTable(
            string tableName = "New Table",
            string tableExpression = "FILTER({BLANK()},FALSE)")
        {
            IEnumerable﹤Table﹥ tables = 
                Model.Tables.Where(t=﹥t.Name == tableName);

            Table table = null;
            
            if(tables.Count() == 0)
            {
                table = 
                    Model.AddCalculatedTable(
                        name: tableName, 
                        expression: tableExpression);
            }
            else
            {
                table = tables.First();
            }
            return table;
        }
    }

A few things about this piece of code. First I replaced < and > by some similar looking character because the wordpress thing that makes it look nice does not play well with these characters (if anyone knows a good way to do it please let me know). Next, this IEnumerable<Table> is a collection of tables. It could be any other type of collection too, you just need to change the type. While it’s true that you can also use «var» as type, this comes with some restrictions of what Visual Studio allows you do do with the variable later on without throwing warnings, so since we have Visual Studio helping out we might as well write proper types for things.

For an initial version this could be enough. If it does what we want, let’s stop it there. Once you have this table creation «encapsulated» (I love this word) you can sophisticate more and more (searching by annotation, making sure that the final table does have the annotation, etc). I learned just today that you could even pass a lambda expression stored as function as an argument to test if certain table exists! So many possibilities.

Let’s connect the pieces!

Right now though we are on a mission to show how to make this class work for us. This method is fairly simple, but once we get creative we might put in a lot more optional parameters, so we really need intellisense. So, now it’s time to go back to the ‘coding environment’ project, the place where we’ll actually write the scripts, and add a reference to our new project, the project where we just created the method. To do that, right-click on the ‘coding environment’ project name > Add Reference

This time though instead of «Browse» we’ll head to «Projects» and there we’ll find our project. Select it and click OK to add it as reference.

Like we did for our other project references, we still need to make it explicit in the code that we are going to use it, so we’ll add an extra «using»

using GeneralFunctions;

Code all you can eat!

From that point on, we can already use our methods with all luxury of intellisense, we just need to type the class name, dot, and the name of the method

That’s already awesome! But remember, we’re still on Visual Studio. How does all this play out when we want to execute it in Tabular Editor?

Wrapping things to Tabular Editor

Well, like we saw in this article, our script is only the code within this faux method, in the last example «ScriptUsingCustomClass». So we can go ahead and copy that to Tabular Editor (normally our scripts will be longer than that, but you get the idea).

Obviously, if we try to execute just that we get the error message «The name ‘Fx’ does not exist in the current context.». Fair enough. So now it’s time to copy-paste our Fx class. Just the class, not the entire namespace. However, now we see some problem… We do not want to copy the Model variable because this will certainly cause problems on the Tabular Editor’s side.  If we write also our functions for Output, Error and so on to avoid the ScriptHelper shenanigan, we get more and more code that we’ll have to remove each and every time. Unless we do something sneaky…

We’ll copy the class declaration line right before the first *real* method and then… we’ll comment it! Then we can select from right after the second forward slash until the end of the class without getting any unneeded piece of code on the way. To make this work properly we’ll bring the curly bracket on the same line.

Then we can paste it after our script in Tabular Editor and bam! it works!! It only creates a table on the first run, because in the second one it already finds it there.

One little thing…

But wait, you might ask «Why are you showing that in TE3? »

We’ll let’s try the same script in TE2

Well that’s unfortunate…but it’s not the end of the world!

Until Daniel Otykier fixes this (which might as well happen before this post goes to press) there is a workaround. The error we are getting is caused by the fact that inside the class the «Model» object is not recognized, or at least it does not point to the Model of our, er, tabular model.

So it’s not fun, but we will need to pass it as argument every time we want the method to make any change to the model, or will call other methods that will. So yes, pretty much always.

What do I mean? We need to modify our class like this:

• We no longer need the Model variable
• We add the model parameter as the first parameter because this one is not optional!

And on the script side we see that intellisense has picked up the change on the definition very quickly

So we just add the model to the list of parameters (If we name the parameters we can pass then in any order we want)

And now let’s copy paste everything back to TE2 and see if it works this time around

Awesome!

The good news is that this code will also work in TE3, so you can make one script and distribute as much as you want. The bad news is that adding Model:Model all over the place is not fun, but hey, the benefits still outweigh the pain by around 1000x. If you want to get it fixed in TE2 (I hope it’s fixable somehow) you can let Daniel know here. When it get’s fixed refactoring your code it’s not that painful either.

Reusable code is awesome

The thing with classes is that most of you will probably think «I do not write that much code to bother with this» but maybe the reason you are now writing more code is that writing code is a pain because you need to take care of all the niceties each and every time. When you know that bit chunks of the code are already there and that you just need to line them up differently, then suddenly you start writing a lot more scripts, but in less time and with more quality than before — because when you know this is the only time you’ll have to code something, you do it better, Much better!

I already built some class, but I might rewrite it with what I’ve learned writing this post. For instance, making all methods return a boolean is not a bad idea, but I don’t think the resulting code is easy to read. Also passing arguments by reference it’s quite a pain in c# since you have to write «out» on the declaration but also when you call the method, which –if you ask me– doesn’t make much sense. VBA handles byref parameters better IMHO, but probably is way worse in many other aspects so I’ll shut up right now.

So after all maybe it’s cleaner to make the method return the object (Table, Measure, whatever) and then on the next line check for null.

Table measureTable = Fx.CreateTable(tableName: "My Measures",Model: Model);
if(measureTable == null) return;

Yep, I think I like this style much better. Oh yes, if you are only going to write a single line for the «then» part of your if statement, you can do it like this, without the curly brackets!

Remember, you can start small, but from experience, before long this class will be your treasure!

Hope you enjoyed this post and looking forward to what you will all will create! In C# like in many other things, the more the merrier!

Follow the conversation on Twitter and LinkedIn

PS: You are trying in your machine and things don’t flow as expected? Come to Reddit and let us know where did you get stuck!

• https://www.reddit.com/r/TE_CSharp/

if you prefer to do it in Spanish we also have a subreddit for that

• https://www.reddit.com/r/TE_CSharp_ESP/