Sample for Host Integration incl custom functions. about power-fx HOT 16 CLOSED

We are adding samples at: https://github.com/microsoft/power-fx-host-samples
It currently has just a repl.

Some of these examples are also via the unit tests: https://github.com/microsoft/Power-Fx/blob/main/src/tests/Microsoft.PowerFx.Interpreter.Tests/RecalcEngineTests.cs

from power-fx.

Hi @tthiery, this is a quite simple implementation on how to evaluate formula using c#.

using System;
using Microsoft.PowerFx;
using Microsoft.PowerFx.Core.Public.Types;
using Microsoft.PowerFx.Core.Public.Values;

namespace Apvee.PowerFx.Console {
  class Program {
    static void Main(string[] args) {
      var simpleFormula = "A + 3 + B + B";
      var filterFormula = "Filter(Accounts; Age>30)";

      var engine = new RecalcEngine();

      var accountType = new TableType()
        .Add(new NamedFormulaType("Age", FormulaType.Number))
        .Add(new NamedFormulaType("Name", FormulaType.String));

      var contextType = new RecordType()
        .Add(new NamedFormulaType("A", FormulaType.Number))
        .Add(new NamedFormulaType("B", FormulaType.Number))
        .Add(new NamedFormulaType("Accounts", accountType));

      var context = FormulaValue.NewRecord(new {
          A = 2,
          B = 5,
          Accounts = new [] {
            new {
              Age = 31, Name = "User 1"
            },
            new {
              Age = 29, Name = "User 2"
            },
            new {
              Age = 33, Name = "User 3"
            }
          }
      });

      var simpleCheck = engine.Check(simpleFormula, contextType);
      if (simpleCheck.IsSuccess) {
        var simpleResult = engine.Eval(simpleFormula, context);
        System.Console.WriteLine(simpleResult.ToObject().ToString());
      }

      var filterCheck = engine.Check(filterFormula, contextType);
      if (filterCheck.IsSuccess) {
        var filterResult = engine.Eval(filterFormula, context);
        System.Console.WriteLine(filterResult.ToObject().ToString());
      }
    }
  }
}

This implementation is in the early-stage phase, I think.
I didn't find anything regarding the state management.

from power-fx.

Another example on using the state management (found by made some tests):

using System;
using Microsoft.PowerFx;
using Microsoft.PowerFx.Core.Public.Types;
using Microsoft.PowerFx.Core.Public.Values;

namespace Apvee.PowerFX.Console
{
   class Program
   {
       static void Main(string[] args)
       {
           var engine = new RecalcEngine();

           var contextType = new RecordType()
               .Add(new NamedFormulaType("A", FormulaType.Number))
               .Add(new NamedFormulaType("B", FormulaType.Number));

           var context = FormulaValue.NewRecord(new
           {
               A = 1,
               B = 5
           });

           engine.UpdateVariable("context", context);
           engine.SetFormula("result", "context.A * context.B", null);
           System.Console.WriteLine(engine.GetValue("result").ToObject().ToString());

           context = FormulaValue.NewRecord(new
           {
               A = 3,
               B = 5
           });

           engine.UpdateVariable("context", context);
           System.Console.WriteLine(engine.GetValue("result").ToObject().ToString());
       }
   }
}

from power-fx.

@MikeStall Is PowerFx completely based on .NET Core or are you planning to release an engine for JavaScript too?

from power-fx.

@MikeStall Is PowerFx completely based on .NET Core or are you planning to release an engine for JavaScript too?

Ok, the timeline it's clear now... https://powerapps.microsoft.com/en-us/blog/power-fx-open-source-now-available/
I think that the Js implementation is very important!

from power-fx.

JS / wasm edition is definitely a need.

Thanks for the samples. I worked in the past on engines like this and got it also running ... not to that extend (there was something during compilation and the translation which stole time).

my big remaining question: how is the lookup done over a non-in-memory model? Efficiently. When we talks millions of records. Or is that out of scope of this engine.

from power-fx.

@fabiofranzini - We have a common front-end (parsing, binding, semantics, Intermediate Representation) and then multiple backends.

• We've open sourced a C# interpreter implementation here.
• Power Apps Canvas apps uses a JScript runtime - but that isn't open source yet.
• Dataverse formula columns actually uses a SQL backend! (https://powerapps.microsoft.com/en-us/blog/power-fx-formula-columns-in-dataverse/)

We plan to make this more open and extensible to enable more backends in the future.

from power-fx.

The current binding for objects into Power Fx requires fully copying the object - so that does not scale to large tables. We do have plans to fix this and essentially add the delegation abilities ( https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview ) to the implementation here.

from power-fx.

Thanks for the conversation and the help here!

from power-fx.

@rajyraman - The custom functions here are currently just for hosts that host the power fx engine themselves.

We do have a preview of component story for Power Apps which lets you effectively create your own functions - see https://powerapps.microsoft.com/en-us/blog/enhanced-component-properties/ for more details.

from power-fx.

Thanks @tthiery for opened this issue! 👍

from power-fx.

@tthiery I think that, with the current implementation here (.net standards), it's possible to execute the interpreter in wasm mode (using for example blazor).

Looking forward for the js implementation ❤️

from power-fx.

@MikeStall thanks for the clarification. That is exactly my worry.

I have prototyped calculation systems with external reference and data models (ensuring topological sorting and data lookups in the core). But at a certain point I was out of luck, time and mood when it came to determine executions instances over a model i do not want to hydrate. Curious how this will work here. The delegation seem more like a linq like translation into native query languages.

from power-fx.

@tthiery - right, Delegation is very similar to linq.

from power-fx.

We are adding samples at: https://github.com/microsoft/power-fx-host-samples It currently has just a repl.

Some of these examples are also via the unit tests: https://github.com/microsoft/Power-Fx/blob/main/src/tests/Microsoft.PowerFx.Interpreter.Tests/RecalcEngineTests.cs

@MikeStall - Is it possible to surface these custom functions back to either Power Apps canvas app, Ribbon or calculated column, or is this restricted entirely to the custom application using Power Fx framework?

from power-fx.

@MikeStall
If we submit custom functions this repo as @rajyraman mentioned will they be considered by the development team for inclusion in the Power FX langauge?

Certainly we don't want to see Power FX filled with 100s of functions which are not used very often. But there are several functions missing from the MS Excel language I'd like to try my hand at contributing

Examples:
CEILING
EOMONTH
FLOOR
LARGE
ROMAN
SMALL

from power-fx.