Gift certificates reports multiply by -1

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}

Anyone?, please help me.

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”

@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.

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. :smiley:

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

1 Like

:smile: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}}
1 Like

Glad to help, I’m sure your SQL skills will be handy at some point in time :wink:
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.

2 Likes

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:

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?

1 Like

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.

Thanks Kendash.

Here an image of my thoughts:

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)?

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.

:disappointed:
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 :smile:

Thanks again pauln & kendash

@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.

1 Like

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 …

Yes sorry that was a different export sample.

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.

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.

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).