Gift certificates reports multiply by -1

[Prodigy]

In the report how to multiply E.Balance.sum to “-1” ?, i have tried the following :

[Gift Certificates:5,5]
>Name|Balance
{REPORT ENTITY DETAILS:E.Name,=-1*E.Balance.sum:(ET=Gift Certificates) && E.AccountId>0:::$2!=0}[/code]

But retuning empty table.

Original Report : 
[code]
[Gift Certificates:5,5]
>Name|Balance
{REPORT ENTITY DETAILS:E.Name,E.Balance.sum:(ET=Gift Certificates) && E.AccountId>0:::$2!=0}
[/code]

Also tried : 
[code][Gift Certificates:5,5]
>Name|Balance
{REPORT ENTITY DETAILS:E.Name,[=-1*(TN('{E.Balance.sum:(ET=Gift Certificates)}'))] && E.AccountId>0:::$2!=0}

But still no cigar.

Neither does :

[code]
[Gift Certificates:5,5]

Name|Balance
{REPORT ENTITY DETAILS:E.Name,=-1*[E.Balance.sum:(ET=Gift Certificates)] && E.AccountId>0:::$2!=0}[/code]
nor :

[Gift Certificates:5,5]
>Name|Balance
{REPORT ENTITY DETAILS:E.Name,=[-1]*[E.Balance.sum:(ET=Gift Certificates)] && E.AccountId>0:::$2!=0}

and :

[Gift Certificates:5,5]
>Name|Balance
{REPORT ENTITY DETAILS:E.Name,=(-1)*[E.Balance.sum:(ET=Gift Certificates)] && E.AccountId>0:::$2!=0}

[Prodigy]

Anyone?, please help me.

[pauln]

Hey @Prodigy
I can get this to work:

([E.Balance.sum]*-1)

But cannot get the Qualifier for Entity to work? Cannot get the Qualifier to work evenwithout the “*-1”

[Prodigy]

@pauln
For me “([E.Balance.sum]-1)" doesn’t work, it return empty report.[quote=“pauln, post:3, topic:8521”]
But cannot get the Qualifier for Entity to work? Cannot get the Qualifier to work evenwithout the "-1”
[/quote]

Do you ‘install’ Gift Certificates by QMcKay ? or having customers/members balance custom field if i am understanding it right. I think you need to make 1 customer/members with balance field on it.

[pauln]

Yea I was just testing using Customers and not Gift Certificates but they are the same thing just difference Entities: The syntax below works for me BUT I do not have any other Entities with Accounts, just Customers. I cannot see why you can not substitute for Entity Gift Certificates. No custom fields just Customer with an Account created, just as Gift Card with Account created.

[List:1,1]
{REPORT ENTITY DETAILS:
	E.Name,
	([E.Balance]*-1);P0.00;N-0.00;Z0.00:
	(ET=Customers) and E.AccountId>0:
	{0} | ${1}}

Using the “.sum” makes no difference WHEN specifying a mutli-column report but if you just list the 1 field it will give you the Total of all Gift Card Accounts - to which I think is not what you desire.

The tricky syntax delving into emre’s mind (oh save me) is the qualifier is at the end of the Syntax as below:

([E.Balance]*-1);P0.00;N-0.00;Z0.00:(ET=Customers) and E.AccountId>0:

The middle stuff is just me seeing how the Positive;Negative;Zero formats work. Thinking as I just wrote this you probably do not need the “*-1”, maybe just use 0.00 for the negative position?

Try without using multiplier:

>Balances x 0
[List:1,1]
{REPORT ENTITY DETAILS:
	E.Name,
	([E.Balance]);P0.00;N0.00;Z0.00:
	(ET=Customers) and E.AccountId>0:
	{0} | ${1}}

No need for “*-1” you can just use the formatting option as above, I have taken out the “N-0.00” and replaced with just “N0.00”.

You can just use E.Balance and ditch the Brackets as no need to multiply by “-1”. The issue is filtering report to just Balances >0 and I think E.balance has an issue.

Must run but found the link I was looking for with regards using “E.Balance>0”:

SambaPOS V 5.1.55 - 5.1.56 Release

E.Balance is a helper field to help you display account balances. This field is not suitable to use as a filter so intentionally not included in expression section.

[Prodigy]

Great, pretty from what i can see and thanks for your helps & kindness, is there any documentation for these tricks?

For those wondering report template for QMcKay’s Gift Certificate :

[code]
[Gift Certificate:1,1]

Name|Balance
{REPORT ENTITY DETAILS:
E.Name,
([E.Balance]);P0.00;0.00;0.00:
(ET=Gift Certificates) and E.AccountId>0:
{0} | $ {1}}[/code]

How to filter out the balance who has <=0?

Member/Customer balance report template :

[Member Balance:5,5,5,5]
>Member ID|Name|Address|Balance
{REPORT ENTITY DETAILS:
	EC.Member ID,E.Name,EC.Address, 
	([E.Balance]);P0.00;0.00;0.00:
	(ET=Members) and E.AccountId>0:
	{3} | $ {4}}

[pauln]

@Prodigy

thanks for your helps & kindness, is there any documentation for these tricks?

Glad to help, I’m sure your SQL skills will be handy at some point in time
Just reallly happy to finally put something back in! I have “banged on a bit” about documentation before, so I will pull my head in here. With this function above there was little, but my recent posts around “Button Captions” sort of discovers and documents this little gem…

The structures and discussion above form the best documentation so far.

[pauln]

Only because “I can’t help myself!”

>Balances no zeros
[List:1,1]
{REPORT ENTITY DETAILS:
	E.Name,
	E.Balance.Sum;P0.00;N0.00;Z0.00:
	(ET=Customers) && E.AccountID>0:
	{0} | ${1}::$2!=0:}

I have no idea why but this also works:
[EDIT] This is wrong as the formats are not obeyed i.e. “$” in “$23.69”.

>Balances no zeros
[List:1,1]
{REPORT ENTITY DETAILS:
E.Name,
E.Balance.Sum;P0.00;N0.00;Z0.00:
(ET=Customers) && E.AccountID>0:::$2!=0:
{0} | ${1}}

Guys were working on some good documentation here:

Customer Loyality points/balance monthly report

Thanks. Is there any documentation explaining what the :::$4!=0 is doing? I want to try understand as much as I can so I can start contributing to this community rather than just asking for help all the time Also why does removing Address or Phone break this? I dont need the address and If i remove it it shows all entities with 0 balance. [Customers:1, 1] {REPORT ENTITY DETAILS:E.Name,E.Balance.sum:(ET=Customers) && E.AccountId>0:::$4!=0} But his works [Customers:1,1,1, 1] {REPORT ENTITY …

Notes:

1. You need to include “.sum” as it must convert E.Balance to Numeric of something internally
2. The $2!=0 means 2nd Column Field not equal to 0.

Still don’t understand the 3 colons ":::" requirement and why when they are placed bottom or 2nd bottom line it works?

[Jesse]

@pauln

Still don’t understand the 3 colons “:::” requirement and why when they are placed bottom or 2nd bottom line it works?

You are seeing three colons because those are placeholders for expressions or other functions. If you see a colon and then nothing after it or another colon it means its skipping that optional formatting. But you have to put them there so it knows what kind of formatting is applied.

Look here and it might make more sense.

Customer Loyality points/balance monthly report

No its specific syntax for the REPORT tags but leaving it blank means its not being used. Ill have to look up the various options give me a minute. This is a quote from a thread Emre made but I copy pasted it here instead of quote so it would show the sections correctly. {REPORT ORDER DETAILS:EN.Officer,O.Quantity.Sum::{0}:,} ^--------------------^-------------------------^^---^-^ So we have 5 sections here Tag Name. Fields. Expression (left empty) Line format (as we have only {0} first fi…

[pauln]

Thanks Kendash.

Here an image of my thoughts:

@Jesse

placeholders for expressions or other functions

Happy with most of my resolutions but lack of examples is what would help as I have no idea what you mean above?
Like I assume the EXPR? in the bove image is a “General Expression” to be used for the result set and not just a field like E.Balance.Sum.

So what are some examples that would go into this TAG for Red Arrow (1)?

[Jesse]

I am just being honest here but I enjoy helping other people solve configurations etc but I really do not enjoy answering documentation questions just for the sake of documenting it. Sorry if that upsets anyone but its the truth. When I see conversations like that it disappoints me a little. Maybe thats selfish I dunno but things like that make me ignore it and move on to more interesting topics.

[pauln]

Its a hard grind and I feel the same. If the documentation was in place then for the “bits & pieces” then I would not be having this conversation as well.

Actually thought, IMO, it was doing more good than boredom. Just follow a few topics about questions raised - same, same - can’t really say “hit the search button” on some of the newer features…

Anyway the last thing I want is for you to hit the “ignore” button, it would be to same respect “game over”. I will do my best to curtail it. Have lots more interesting stuff to get on with

[Prodigy]

Thanks again pauln & kendash

[emre]

@pauln arrow (1) is row format.

Col Formats (Line Format) section is used to join fields to construct lines. Row Format section is used to join lines to construct result.

you can see a sample usage here.

Data Exports feature

I clipped this from a Staff discussion on this feature. The following is all quoted by @emre. SYNTAX {REPORT ORDER DETAILS:<expression>:<line format>:<delimiter>} Orders XML <orders> {REPORT ORDER DETAILS: O.MenuItemName, O.Quantity, O.Price, ([O.Quantity]*[O.Price]-[O.ExactTotal]), O.ExactTotal :O.MenuItemName.Contains("Toasted") : <order> <name>"{0}"</name> <quantity>{1}</quantity> <price>{2}</price> <service>{3}</service> <total>{4}</total> </order>} </ord…

[pauln]

@emre

you can see a sample usage here

Thanks for getting back on this.
Correct if I am wrong there is actually no usage (examples) in that thread as they all truncated after the "}" belonging to the {REPORT:tag }?

Thats ok, however, it seems we can have horizontal build of lines i.e. Line 1 + Line 2 + Line …

[emre]

Yes sorry that was a different export sample.

Batch Product Creation with YAML

This Data Export template should work. @gerlandog if you have non default products can you please paste it into the editor, export a file and send me that file? [DataExport:0] @{REPORT MENU ITEM DETAILS:M.Id::|} {REPORT MENU ITEM DETAILS:M.Name,M.GroupCode,M.Barcode.M.Tag:M.Id == $1: - Name\: {0} GroupCode\: {1} Barcode\: {2} Tag\: {3} } {REPORT PORTION DETAILS:P.Name,P.Price:P.MenuItemId == $1: - Name\: {0} Price\: {1}: Portions\: {0}} {REPORT MENU ITEM TAG DETAILS:T.Name,T.Value…

So seems like we’re using that section to implement a formatting for entire result. You’ll see it adds -Portions: keyword to the beginning of portions listing.

@pauln

Thats ok, however, it seems we can have horizontal build of lines i.e. Line 1 + Line 2 + Line …

If you end a report tag with a section that contains a single character that will be processed as delimiter. So having :| at the end of tag will join lines as Line1|Line2|Line3.

[pauln]

@emre

You’ll see it adds -Portions: keyword to the beginning of portions listing

To be honest I think I will drop it emre. That example also does not use any “Row Format Section” - Red Arrow (1) above. All those ":" (colons) I think are Escaped using \: ? That would mean they just are part of the “Line Formats” and the Tag is then terminated with the Brace "}"?

All I was chasing is the above picture I have highlighted - what are some examples of syntax that I can use in that spot… - Red Arrow (1).