Have been asked to export email addresses from customer account data.
I can see in the forum it talks about exporting sales info from the database using the data export tool, so I am assuming that we can also export customer records?
Not a SQL guy myself, so I wouldnt have any idea where to start.
Is someone able to help me here?
(btw, I haven’t disappeared, I’ve got a new project on atm {again})
Data export can take normal custom report expressions not just SQL.
A CSV of customer details is pretty easy, not at PC but have basic report if you get stuck.
I gave it a whirl, but the report just looked pretty fkd and didn’t show the details I want.
Also shows reports from all entities, only want from 1 type
Customer Details
Name,Address,Phone Number,e-mail address
{REPORT ENTITY DETAILS:E.Name,EC.Address,EC.Phone Number,EC.e-mail address::{0},{1}, {1},{1}}
Did I broken it?
It saves correctly, just don’t show up right.
Matt
Youll need to add a constraint expression to entity type etc to set to only be one type of entity.
As for output how do you mean fkd? are you setting the export directory as a .csv file?
Data Exports and Reports are really the same thing. They use the same syntax and tags. The only difference is that a Report shows on-screen (an/or can be printed), while a Data Export saves it’s output to a file.
So get the Report working to show the data you want, then worry about the formatting. As @JTRTech mentions, you need to apply a filter for the Entity Type. This comes after the first colon. This should work:
(ET=Customers)
The formatting is the part after the final colon where you choose your fields and delimiters, and is the easy part - you use commas (,) or semi-colons (;) to separate the fields instead of pipes (|).
Export CSV
Customer Details
Name,Address,Phone Number,e-mail address
{REPORT ENTITY DETAILS:E.Name.asc,EC.Address,EC.Phone Number,EC.e-mail address:(ET=Customers):{0},{1},{2},{3}}
Export CSV in Excel
Report
Im still stuck between wether i like you leading me in the right direction to learn myself or not lol.
Thank guys, will give a go when home.
Matt
EIT: I didn’t expect you to do that @QMcKay thank you
so (ET=Entity Type) ?
I successfully exported a Work Period Report to a csv file but how do you export a report that uses a script?
[Sales By Server:1,1,1]
Name|Total|Share
@@BarTipsForExport
SELECT O.CreatingUserName AS Name, CONVERT(nvarchar, CAST(SUM(O.Price * O.Quantity) AS Money), 1) AS Total, CONVERT(nvarchar, CAST(SUM(O.Price * O.Quantity * 0.08) AS Money), 1) AS Share
FROM dbo.Orders AS O INNER JOIN
dbo.MenuItems AS MI ON MI.Id = O.MenuItemId
WHERE (MI.GroupCode = ‘Beer’ OR
MI.GroupCode = ‘Beverage’ OR
MI.GroupCode = ‘Wine’ OR
MI.GroupCode = ‘Liquor’ OR
MI.GroupCode = ‘Cocktail’ OR
MI.GroupCode = ‘Open Bar’) AND (O.DecreaseInventory = 1) )
GROUP BY O.CreatingUserName
The report works fine but I can’t get the export to work. Any help would be appreciated.
You would need to convert it to use the SQLDETAILS report tag. It won’t work with pure SQL.
I tried the syntax below but still no luck.
Name,Total,Share
{Report SQL DETAILS:@@BarTipsForExport:
F.Name,
F.Total,
F.Share:
:"{0}","{1}","{2}"}
Report Tags are case-sensitive, as are most things in SambaPOS.
When you say “no luck” that isn’t telling us anything. Do you get no output? Do you get incorrect output?
This works …
[Sales By Server:1,1,1]
EmpName,Total,Share
{REPORT SQL DETAILS:@@BarTipsForExport:
F.Name,
F.Total,
F.Share:
:"{0}","{1}","{2}"}
Thank you. Your syntax does work. I can’t believe the amount of time I spent on something so simple.
Hi Could you kindly share the template.
Thank you in advance
Regards