I just discovered the serverformulafill method in the documentation, and am super excited about it. I’ve already implemented it in a couple of places and am pleased by how much it speeds up the code. But I have a lot of select/formulafill cases involving lookups that I’d love to replace with serverformulafill and I am running into trouble. When I run this code below, I get the error “Selection formula cannot contain any variables.”
Field "Intended"
serverformulafill «» <> lookupselected("SubWizard","Item to Ship", Item, "ShWt", 0,2), lookup("SubWizard","Item to Ship",Item,"ShWt",0,2)
There are not any variables in the formula, FWIW, but I assume there’s a problem with doing a lookup in another database. Is there any way I could use execute{} to make this work? I successfully used execute{} in a simpler serverformulafill, but am not sure how to make it work for a lookup.
Please understand that there is no free lunch. The serverformulafill will speed up the initial run of the program, but every record it modifies will have to be synchronized from the server to every client computer, including the one that ran the program. So you still definitely want to be very careful to minimize the number of records modified when working in a multi-user environment.
As for your specific code, using the execute statement is not going to help. The problem is that the server has no idea what the value of any variables are. Remember, this statement works by copying the formulas to the server and running them there, so the formulas have to make sense in the context of the server computer.
I think the problem might be the use of «» to indicate the current field. There is no current field on the server, so this won’t work. Perhaps if you change «» to Intended it might work. (And yes, I realize that if this is the problem, the error message isn’t really the correct message.)
Was that to incorporate a variable into the formula, by turning the variable into a constant? That will definitely work.
Your proposed code has another problem – the use of the lookupselected( function. Presumably you have pre-selected the records you are interested in on the local computer. But the server computer knows nothing about this pre-selection, on the server, all records are always selected. So lookupselected( will work just like lookup(, which I assume means it will not work properly for you.
The serverformulafill statement is intended as a possible optimization, which in some cases can improve performance. But it is very tricky to use for anything beyond a simple fill, and when you include the extra time needed for synchronization the performance gain is not as much as you might think. I think this particular application is beyond what serverformulafill is capable of.
A couple of notes about Panorama X Server:
The formulafill statement has been optimized to be much faster than before.
The serverformulafill statement now allows variables. (This would not affect the issues that are causing problems in this particular example, but may be useful to know in general going forward.)
Thank you for the detailed explanation. I will give up on this particular implementation, but I think serverformulafill will still turn out to be very useful in some of our more simple use cases. Thanks!