Advanced form saving/ reading 2 lists
Hello community!
I am just getting familiar with Rich Forms add-in and I would like to ask you if the scenario described below can be done (if yes - how?) using the app.
I have two lists - "Meetings" and "Room reservations". Meeting is a "header" for the room reservations and therefore one Meeting can hold one or more Room reservations.
I would like to create a form, where requester can type the Meeting details (which then should be saved to the "Meetings" list) and then click to add one or more block, holding group of fields:
- Reservation from
- Reservation to
- Room
To define list of rooms and their reservation time spans. That list of rooms should then be saved to the "Room reservations". Moreover I would like to somehow attach a script, that would filter out rooms, which are not available for the chosen time-span.
Then, when a Meeting form is displayed/ edited, list of related room reservations should be available as well.
Any input is welcome
Regards,
Tomasz
-
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 -
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 -
Hi Daniel!
Is there some way to get this to work with two lookup columns on the same sublist?
Best Regards
Joakim
0 -
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 -
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.
Comments
5 comments