Skip to main content

Search

Advanced form saving/ reading 2 lists

Comments

5 comments

  • Daniel Wyss

    Hi Tomasz

     

    Actually this is an ideal scenario for sub lists in Rich Forms. See here: Insert Sublists

    Only challenge here will be filtering the available rooms in the Room lookup on the sublist. I guess you should be able to do it using a custom script. Let me check and follow up on this when I have more details.

     

    Regards

    Dany

    0
  • Daniel Wyss

    Hi Tomasz

     

    Finally got to preparing a sample script for setting the available lookup values in a sub list:

    SP.SOD.executeOrDelayUntilScriptLoaded(function () {

        // **** Define ID of view and internal field name of the lookup name here:
        var viewId = "{40F0AA86-30CD-4658-A0C2-9F98FFF5E3DB}";
        var lookupFieldName = "MyLookup";

        var initJsObject = window.g_SPGridInitInfo[viewId].jsInitObj;
        var lookupfieldInfo = initJsObject.splookupFieldInfo[lookupFieldName];
        var lookupPrpertyType = new window.SPG.LookupPropertyType(lookupFieldName, lookupfieldInfo);
        lookupPrpertyType.GetItems = GetValidLookupItems;

        if (!SP.JsGrid.JsGridControl._bStaticInit) {
            SP.JsGrid.Internal.Property = new SP.JsGrid.Internal.Property;
            SP.JsGrid.JsGridControl._bStaticInit = true
        }
        window.SP.JsGrid.PropertyType.RegisterNewCustomPropType(lookupPrpertyType, window.SP.JsGrid.DisplayControl.Type.Text, window.SP.JsGrid.EditControl.Type.ComboBox);

        var oldRegProperty = window.SP.JsGrid.PropertyType.RegisterNewCustomPropType;
        window.SP.JsGrid.PropertyType.RegisterNewCustomPropType = function (fType, controlType, control) {
            if (fType.ID != lookupFieldName + '_Lookup') {
                oldRegProperty(fType, controlType, control);
            }
        }
    }, 'spgantt.js');


    // **** The following function calculates the valid lookup values. It must call callbackFunc with an array of items created using SP.JsGrid.Property.MakeProperty(<id>, <value>, true, true, this).
    function GetValidLookupItems(callbackFunc) {

        function onQuerySucceeded(sender, args) {
            var resultData = [];
            var listItemEnumerator = collListItem.getEnumerator();
            while (listItemEnumerator.moveNext()) {
                var oListItem = listItemEnumerator.get_current();
                // Add a value to the available lookup values
                resultData.push(window.SP.JsGrid.Property.MakeProperty(oListItem.get_id(), oListItem.get_item('Title'), true, true, this));
            }
            callbackFunc(resultData);
        }

        function onQueryFailed(sender, args) {
            alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
            callbackFunc([]);
        }

        try
        {
            var collListItem = null;
            var context = SP.ClientContext.get_current();
            var oList = context.get_web().get_lists().getByTitle("SampleMasterData");

            var dateISOString = new Date(Ardevia.Helpers.FormControlEventsHelper.GetValueCallbacks['SomeDate']()).toISOString()

            var camlQuery = new SP.CamlQuery();
            camlQuery.set_viewXml('<View><Query><Where><Geq><FieldRef Name=\'ValidFrom\'/>' +
                '<Value Type=\'DateTime\'>' + dateISOString + '</Value></Geq></Where></Query><RowLimit>100</RowLimit></View>');
            collListItem = oList.getItems(camlQuery);
            context.load(collListItem);
            context.executeQueryAsync(Function.createDelegate(this, onQuerySucceeded), Function.createDelegate(this, onQueryFailed));
        } catch (ex) {
            alert('Error retrieving valid lookup values: ' + ex);
            callbackFunc([]);
        }
    };

    Replace the viewId and lookupFieldName variable values with your specific values. To get the view ID for the sub list it is probably easiest to select "Edit Web part" from the sub list web part's context menu, then "Edit current view" in the web part properties and then inspect the opened iFrame where you'll find the View= in the URL query string.

    You will also need to implement the GetValidLookupItems(callbackFunc) method depending on your needed business logic. It should call callbackFunc with an array of items created using SP.JsGrid.Property.MakeProperty(<id>, <value>, true, true, this). In the example I'm just querying the lookup list for any items that has ValidFrom field greater or equal to the entered SomeDate field on the form.

    Just place this JavaScript code into your form in a Script Editor web part (surrounded by <script type="text/javascript">...</script>).

    Hope this helps.

     

    Regards

    Dany

    0
  • Joakim Thell

    Hi Daniel!

    Is there some way to get this to work with two lookup columns on the same sublist?

     

    Best Regards

    Joakim

    0
  • Christof Nussbaumer

    Hi Joakim Thell

     

    It's javascript so nearly everything is possible to implement

    Did you already bring it to work with one lookup? 

    Then next step would be to duplicate the lookupfield specific parts of the script for the second lookupfield.

     

    If you need help I can offer you to book our fee-based expert services to help you in this case: https://outlook.office365.com/owa/calendar/SPAddIns@skybow.com/bookings/

     

    Btw there is an idea (feature request) to have this functionality in future as an easy configurable feature in Rich Forms - here you can vote for: Filter & order single / multiple lookup fields in lists / sublists

    0
  • Joakim Thell

    Hi Christof Nussbaumer

    I'll give it another try with duplicating the lookup specific parts. Already voted for the feature request

     

    Thanks for the info aout your expert services, good to know. 

     

    BR

    Joakim

    0

Please sign in to leave a comment.