In 5.53 there was a function that allowed you to calculate the total sum of a field by using variables. In DE8 this will not function, it will accept the script provided the "with" is removed and in doing so removes the variables. Is there an alternative method

Example

sum of Management Cash Book "Folio" = Folio and "TenancyID" = TenancyID and "Credit_Type" = Rent and "Date" <= CurrentDate ) Credit

Download Sample

I think you now are falling in the trap of thinking everything was better in DFD… ;-). You have never been able to use with statements in derivations (Just tested it to make sure). For some reason it accepts it but it doesn’t kick in. (same goes for DFW). We have been discussing it internally if we are to introduce it. It would be brilliant if one could create on the fly Relationships like that. However..

One of the challenges with DataEase is that it starts with being easy and then it hit you in the back of your head later on if you are not careful ;-)

The problem here is that you get fooled by thinking you are in control when in fact DataEase is. DataEase is a true relational database in the sense that like in universe everything is relative in DataEase everything is relational…

What you see is not what’s going on inside DataEase ;-)

DataEase cache and buffer a lot to make it fast and so you can access data from anywhere, and for this it uses relationships. In the form/table world all the relationships are pre-loaded and pre-defined.

However in the DQL world, everything is different. DQLs are transactional i.e. starts in one end and run till the end. They have a much wider “repertoire” of functionality they can use to do their magic like ad-hoc relationship (with) but what it does is the same as the form bit when it loads the pre-defined relationships. It create a relationship in memory that the multi-view then use.

DataEase do another thing to make things easy that has a tendency to hit you in the head. It will name the relationship after the table/form if you don’t give it an alternative name. And it is this functionality that has both helped you and now causes problems for you.

The with statement didn’t work in DFD either but you had a relationship defined (called the same as the table) with the correct restrictions to give you the result you want and that is the obvious solution for you again, but this time to do it by defining that relationship, giving it an alternative name and then use that name instead of the table name. Then you know exactly what relationship you use and the restrictions you are applying.

3. The reason your sum of don’t kick in is the same, you already have a relationship called the same as the table with a retriction and when you use sum of “table name” you don’t do that, but you do sum of “relationship called the same as table” which then have a restriction you don’t see.

The correct syntax is to give this relationship an alternative name too:

sum of MyTable named “my relationship” with etc…

Sum of is not a function or a command but an statistical operator in line with count of, mean of etc. so you won’t find it in the functions or command list but in the operators list (where you find lookup too).

For fun I have created a small sample that do exactly what you want and to illustrate I have not defined any relationship between the MainForm and the SomeDate tables in the relationship form so you can see the relationships being created “ad hoc” in the form too via ExecDQL.

The ExecDQL looks a little “crooked” because we need to use “ in the string and the only way to do that at the moment is to concatenate in chr(34). We are looking to include /” or use the same alterntive as JavaScript ‘ (when inside “”).

We will see but for now you have to do it this way or our preferred way which is MemoExecDQL(), storing the DQLs in a ExecDQLStore table and simply look it up into a virtual Memo field and executing it from there.

ExecDQL(concat("define ",chr(34),"retval",chr(34),"text . retval:=SetValue(", chr(34),"Sum",chr(34),", sum of SomeDate with Mykey=data-entry field1 and SecondKey=data-entry field2 and Date <= data-entry field3 price ) ."),MyKey ,SecondKey ,DateField,"","")+SetState("Manipulator",0)

What we do here is calling the DQL, in the DQL we exploit the double context of ExecDQL and use SetValue(“Sum”… which is the field we want the value to be in (in the form) and simply do the sum of with…

Simples!

To illustrate we have done it directly with ExecDQL and also with MemoExecDQL looked up.

Start with MainForm and decode…