viewFoundSet in relationship and from another VFS

[rafig]

I hope everyone is safe & well...
I am trying to configure a form based on a 'viewFoundSet' (VFS).[ I wanted to use a VFS as it allows me display aggregated records from complex criteria. In this case, how many of a particular stock item to 'pick' for a particular transport route.]
This is fine, but I am now trying to display some related records to the 'record' in VFS I am showing.[This is to allow the picker to have that total broken down in to total number for each client on that route]
(1) I tried to create a relationship with the form VFS as datasource on the left of the relationship with some of the columns from it relating to a table as datasource on the right.
Servoy allows me to do this, but nothing is displayed when I move thru the records in my VFS on form in a ServoyExtra Table using the relationship as the foundset.
(2) I then, instead, tried to create a 2nd VFS that I load 'onRecordSelection' as the records of the 1st VFS are moved thru.
This fails with the following error

Code: Select all

ERROR com.servoy.j2db.util.Debug - null (Form Context: picking), JSEvent(type = action, source = Component: <'btnItemRoute' of parent FormUI for FormController[form: picking, fs size:3, selected record: Record[DATA:Row(db:/aquarius/transportroutes)[DATA:tennant_id=null,creation_datetime=2020-07-27 01:00:32.0,modification_datetime=2020-07-27 01:00:32.0,creation_username=AUTOIMPORT,modification_username=AUTOIMPORT,transportroutecode=DFDS(MORR),description=DFDS (MORRISONS),invoiceformat=,invoiceemailformat=,priority=0,monday=1,tuesday=1,wednesday=1,thursday=1,friday=1,saturday=1,sunday=1,minimumordervalue=0.0,minimumordercharge=0.0,packingtimeday=0,mondaypriority=0,tuesdaypriority=0,wednesdaypriority=0,thursdaypriority=0,fridaypriority=0,saturdaypriority=0,sundaypriority=0,transportroutes_id=A95E67D1-BC68-48FB-8775-D3BD57BA0F24,latestpackingtime=null,deliverydays=null,  CALCULATIONS: {checkBoxSelected=<i class="fal fa-check-square menu-icon"></i>, is_selected_2=1, is_selected=java.lang.Object@107954d0, description_with_icon=java.lang.Object@107954d0}]]  COLUMS: tennant_id,creation_datetime,modification_datetime,creation_username,modification_username,transportroutecode,description,invoiceformat,invoiceemailformat,priority,monday,tuesday,wednesday,thursday,friday,saturday,sunday,minimumordervalue,minimumordercharge,packingtimeday,mondaypriority,tuesdaypriority,wednesdaypriority,thursdaypriority,fridaypriority,saturdaypriority,sundaypriority,transportroutes_id,latestpackingtime,deliverydays,getChangedData,getDataSource,getException,revertChanges,isEditing,save,getPKs,isNew,hasChangedData,rollbackChanges,isRelatedFoundSetLoaded,getFoundset,deleteRecord,,destroyed:false]>, formName = picking, elementName = btnItemRoute, timestamp = 2020-11-02 12:40:14.602,modifiers = 0,x =1395,y = 1289,data = null) java.lang.NullPointerException: null
   at com.servoy.j2db.server.ngclient.property.FoundsetTypeSabloValue.updateFoundset(FoundsetTypeSabloValue.java:367) ~[?:?]
   at com.servoy.j2db.server.ngclient.property.FoundsetTypeSabloValue.attachToBaseObject(FoundsetTypeSabloValue.java:254) ~[?:?]
   at org.sablo.BaseWebObject.onPropertyChange(BaseWebObject.java:878) ~[sablo_2020.6.1.3583.jar:?]
   at org.sablo.BaseWebObject.propertiesInitialized(BaseWebObject.java:1223) ~[sablo_2020.6.1.3583.jar:?]
   at com.servoy.j2db.server.ngclient.ComponentFactory.createComponent(ComponentFactory.java:111) ~[?:?]
   at com.servoy.j2db.server.ngclient.WebFormUI.init(WebFormUI.java:170) ~[?:?]
   at com.servoy.j2db.server.ngclient.WebFormUI.<init>(WebFormUI.java:131) ~[?:?]
   at com.servoy.j2db.server.ngclient.component.WebFormController.initFormUI(WebFormController.java:103) ~[?:?]
   at com.servoy.j2db.server.ngclient.component.WebFormController.<init>(WebFormController.java:90) ~[?:?]
   at com.servoy.j2db.server.ngclient.NGFormManager.leaseFormPanel(NGFormManager.java:273) ~[?:?]
   at com.servoy.j2db.server.ngclient.NGFormManager.leaseFormPanel(NGFormManager.java:1) ~[?:?]
   at com.servoy.j2db.scripting.CreationalPrototype.get(CreationalPrototype.java:168) ~[servoy_shared_2020.6.1.3583.jar:?]
   at org.mozilla.javascript.ScriptableObject.getProperty(ScriptableObject.java:2357) ~[org.eclipse.dltk.javascript.rhino_2020.6.1.3583.jar:?]
   at org.mozilla.javascript.ScriptRuntime.getObjectProp(ScriptRuntime.java:1557) ~[org.eclipse.dltk.javascript.rhino_2020.6.1.3583.jar:?]
   at org.mozilla.javascript.ScriptRuntime.getObjectProp(ScriptRuntime.java:1550) ~[org.eclipse.dltk.javascript.rhino_2020.6.1.3583.jar:?]
   at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1248) ~[org.eclipse.dltk.javascript.rhino_2020.6.1.3583.jar:?]
   at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:815) ~[org.eclipse.dltk.javascript.rhino_2020.6.1.3583.jar:?]
   at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:109) ~[org.eclipse.dltk.javascript.rhino_2020.6.1.3583.jar:?]
   at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:399) ~[org.eclipse.dltk.javascript.rhino_2020.6.1.3583.jar:?]
   at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3534) ~[org.eclipse.dltk.javascript.rhino_2020.6.1.3583.jar:?]
   at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:107) ~[org.eclipse.dltk.javascript.rhino_2020.6.1.3583.jar:?]
   at com.servoy.j2db.scripting.ScriptEngine.executeFunction(ScriptEngine.java:672) [servoy_shared_2020.6.1.3583.jar:?]
   at com.servoy.j2db.debug.RemoteDebugScriptEngine.executeFunction(RemoteDebugScriptEngine.java:393) [servoy_debug_2020.6.1.3583.jar:?]
   at com.servoy.j2db.server.ngclient.component.EventExecutor.executeEvent(EventExecutor.java:252) [servoy_ngclient_2020.6.1.3583.jar:?]
   at com.servoy.j2db.server.ngclient.DataAdapterList.executeEvent(DataAdapterList.java:126) [servoy_ngclient_2020.6.1.3583.jar:?]
   at com.servoy.j2db.server.ngclient.WebFormComponent$FormcomponentEventHandler.executeEvent(WebFormComponent.java:241) [servoy_ngclient_2020.6.1.3583.jar:?]
   at org.sablo.BaseWebObject.doExecuteEvent(BaseWebObject.java:335) [sablo_2020.6.1.3583.jar:?]
   at org.sablo.BaseWebObject.executeEvent(BaseWebObject.java:324) [sablo_2020.6.1.3583.jar:?]
   at org.sablo.services.server.FormServiceHandler.executeEvent(FormServiceHandler.java:137) [sablo_2020.6.1.3583.jar:?]
   at com.servoy.j2db.server.ngclient.NGFormServiceHandler.executeEvent(NGFormServiceHandler.java:549) [servoy_ngclient_2020.6.1.3583.jar:?]
   at org.sablo.services.server.FormServiceHandler.executeMethod(FormServiceHandler.java:84) [sablo_2020.6.1.3583.jar:?]
   at com.servoy.j2db.server.ngclient.NGFormServiceHandler.executeMethod(NGFormServiceHandler.java:528) [servoy_ngclient_2020.6.1.3583.jar:?]
   at org.sablo.websocket.WebsocketEndpoint$5.run(WebsocketEndpoint.java:408) [sablo_2020.6.1.3583.jar:?]
   at org.sablo.eventthread.Event$1.run(Event.java:102) [sablo_2020.6.1.3583.jar:?]
   at org.sablo.websocket.CurrentWindow.runForWindow(CurrentWindow.java:76) [sablo_2020.6.1.3583.jar:?]
   at org.sablo.eventthread.Event.execute(Event.java:92) [sablo_2020.6.1.3583.jar:?]
   at org.sablo.eventthread.EventDispatcher.dispatch(EventDispatcher.java:125) [sablo_2020.6.1.3583.jar:?]
   at org.sablo.eventthread.EventDispatcher.run(EventDispatcher.java:89) [sablo_2020.6.1.3583.jar:?]
   at com.servoy.j2db.server.ngclient.eventthread.NGEventDispatcher.run(NGEventDispatcher.java:55) [servoy_ngclient_2020.6.1.3583.jar:?]
   at java.lang.Thread.run(Unknown Source) [?:?]


For point (1), is it possible to use a VFS in a relationship, and if so, why is that not working?
For point (2), am I pushing Servoy too far by trying to select data for a 2nd VFS from 1st VFS (or what is meaning of the error I displayed?
Thanks
Rafi

[rafig]

In answer to my own post, after deeper experimenting, it seems like is possible to use a VFS to provide the data for a 2nd VFS.
HOWEVER, it is very difficult to get this to work.
When I made my original post, I had the 'onLoad' method for the 2nd VFS in the form methods (let's call it 'pick_list'), but this did not allow the 'onLoad' method in the 2nd VFS entity to be set, as it wasn't in a global scope (so I just didn't set it...)
I then thought this might be the issue, so I then copied that method in to the same global scope that had the onLoad method for the 1st VFS and tried to reference the 'pick_list' foundset columns.
This did not work, as the scope returned 'undefined' for each column I was trying to use.
BUT, if I used a button on form 'pick_list' to use that form method to load 2nd VFS, it could get to the values and then would load the 2nd VFS.
To get to test this I actually had to delete the table element using the 2nd VFS from the form so it would actually display, then, once loaded and displayed, I put table back on form in developer, saved form, it then displayed [although code still ran without table on form]
So, to try & clarify
1) I have a form, 'picking' where some values are set that are the search criteria for VFS1. There is a button on form that, when values are chosen/set (like 'stockcode'), loads VFS1 and tries to show form 'pick_list'

Code: Select all

      scopes.scanner.onLoadPicking("pickingitems");
      forms.pick_items.controller.show();


2) form 'pick_list' is based on VFS1, displaying each record one at a time
3) form 'pick_list' is meant to have a table on it displaying data from VFS2 that uses the record shown from VFS1
4) I cannot display 'pick_list' form if the VFS2 table is on it.

Code: Select all

ERROR com.servoy.j2db.util.Debug - Executor,uuid:5971B03:2: For form FormController[form: pick_items,destroyed:false] the foundset/elements were asked for but that was not (or was no longer) set.

<- that error is from a test in the scope onLoad code for VFS2

Code: Select all

if (forms.pick_items.foundset && forms.pick_items.foundset.getSize() > 0) {

It's as if the VFS really does not 'exist' until viewed (a bit like Schrödinger's cat). Removing table allows form to display.
5) Putting table back on form once it's shown and then 'loading' VFS2, it works, but ONLY if I use the method in form code,

Code: Select all

query.where.add(query.columns.stockcode.eq(stockcode));

NOT using the scope method, which does not seem to be able to access foundset values

Code: Select all

query.where.add(query.columns.stockcode.eq(forms.pick_items.foundset.stockcode));

So I just don't understand how I can get this to work
( I would prefer option (1) from my original post to work, i.e. get relationship to work from VFS1...)

I hope someone from Servoy might be able to help now
Thanks

[rafig]

[UPDATE 2]
for the moment I have changed the table on form 'pick_items' to use an 'In Memory Datasource' that I am loading in the same way I was VFS2 and this seems to be working