SetValue Using Query

samevedzi

Hi,

Can I set the default value of a field using one of the values from the fields submitted by a user in a query as in the script below?

editor.Field(
new Field( "Unit_Price" )
.Set( Field.SetType.Create )
.SetValue( "SELECT Unit_Price FROM Item WHERE ID = [Form].[Item_ID]" )
);

Thanks for your help.

allan

You can't do it quite like that I'm afraid, but the same effect is possible in a slightly different way. The key is to keep in mind that SetValue() will write in the value you give is (it doesn't try to interprate any way), so we need to run the query and then set the result as the value - e.g.

editor.PreCreate += (sender, e) => {
  var res = e.Editor.Db()
   .Select("Item", "Unit_Price", query => query.Where("ID", ?))
   .Fetch();

  e.Editor.Field("Unit_Price").SetValue(res["Unit_Price"]);
};

Hopefully I've not made any syntax errors! It's actually been a little while since I've written C#! This is the key page in the documentation for the events.

One thing I wasn't sure about is your WHERE condition. What is Form.Item_ID? Is that something being submitted from the client-side?

Allan

samevedzi

Hi Allan,

Thanks for your response.

Yes, Form.Item_ID is submitted from the client-side. Should that go in the place of the question mark (?) on line number 3? Is Form.Item_ID the appropriate way of getting values submitted from the client-side?

Thanks,
---Stephen

allan

e.Values["Item_ID"] should do it. e.Values is a Dictionary of the values that have been submitted for that row.

Allan

samevedzi

Hi Allan,

Thanks again for the response.

I tried the script below:

editor.PreCreate += (sender, e) =>
{
    var res = e.Editor.Db()
        .Select("Item", "Unit_Price", query => query.Where("ID", e.Values["Item_ID"]))
        .Fetch();
    e.Editor.Field("Unit_Price").SetValue(res["Unit_Price"]);
};

I get the error message 'CS1660: Cannot convert lambda expression to type ‘Dictionary<string, dynamic>’ because it is not a delegate type' on the .Select(...) line.

What have I done wrong.

Stephen

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

Sorry - I think the string as the second parameter to Select has it confused - try:

editor.PreCreate += (sender, e) =>
{
    var res = e.Editor.Db()
        .Select("Item", new[] {"Unit_Price"}, query => query.Where("ID", e.Values["Item_ID"]))
        .Fetch();
    e.Editor.Field("Unit_Price").SetValue(res["Unit_Price"]);
};

Allan

samevedzi

Hi Allan,

Thanks for the response.

I get the System.Collections.Generic.KeyNotFoundException: 'The given key was not present in the dictionary.' error when I tried the script as shown below.

                        var res = e.Editor.Db()
                            .Select("dbo.Item", new[] { "Unit_Price" }, query => query.Where("ID", e.Values["Order.Item_ID"]))
                            .Fetch();
                        e.Editor.Field("Order.Unit_Price").SetValue(res["Unit_Price"]);

If I replace e.Values["Order.Item_ID"] with an absolute value, say 4, it works fine.

The json submitted is as below:

{
    "data[0][Order][Date]": "2021-04-27",
    "data[0][Order][Customer_ID]": "2",
    "data[0][Order][Item_ID]": "4",
    "data[0][Order][Unit_Price]": "10",
    "data[0][Order][Qty]": "5",
    "data[0][Order][Barcode_List]": "123,124",
    "action": "create"
}

Thanks,
---Stephen

allan

Ah yes, when using a dot in the field names, it will submit as nested objected - so:

e.Values["Order.Item_ID"]

should actually be:

e.Values["Order"]["Item_ID"]

Allan

samevedzi

Hi Allan,

Thanks once again for the response.

I tried e.Values["Order"]["Item_ID"], but got the CS0021: Cannot apply indexing with [] to an expression of type ‘object’ error this time round.

Thanks,
---Stephen

allan

Oh sorry! We need a cast:

var order = e.Values["Order"] as Dictionary<string, object>;
var itemId = order["Item_ID"];

Allan

samevedzi

Hi Allan,

Thanks very much for all the responses. My issue has been resolved.

Regards,
---Stephen