Export customers details name phone etc to excel

[sangeethrayaroth]

@JTRTech
Hi ,
In my report, it is showing all the entities.
That is, the table numbers, the tocken numbers for take away etc.
And is there any way to export it as excel file?

[JTRTech]

You would want to add an entity type= constraint expression in the second :: section.

[sangeethrayaroth]

Hi JTR , I couldnt understand

[sangeethrayaroth]

Can you please explain a bit?

[JTRTech]

In the expression for reports you have sections between :colons.

{Report Type:Fields:constraints:column sorting:sure there are others}

Am not at PC to check exact expression but after field you constrain with things relative like report for ticket details might constrain to TT which stands for ticket tag in the form of (TT.TagTypeName=Value)

[sangeethrayaroth]

Thankyou JTR. Let me check it.

[JTRTech]

First hit on search for entity details report…

REPORT ENTITY DETAILS:x use of .Sum

Hi Emre - Reviewing this Template syntax I discovered it is not implemented for this type of usage? [Customer Balances:1,2,3, 1] {REPORT ENTITY DETAILS:E.Name,EC.Full Name,EC.Address,E.Balance.Sum;0.00;-0.00;0.00:(ET=Customers) && E.AccountId>0:{0}|{1}|{2}|${3}::$4>0:} >> TOTAL||| {REPORT ENTITY DETAILS:E.Balance.Sum;0.00;-0.00;0.00:(ET=Customers) && E.AccountId>0:${0}::$1>0:} Basically E.Balance.Sum seems to not like $1 place marker using syntax $1 > 0 or $ < 0? RESULTS: [image] The thin…

[mohammed9q9]

Hello
Could you advise on the syntax if the custom field type has “.” as below
I am unable to get the Road No. in the report as below

[JTRTech]

hmm, not sure on easy answer.
Could resulve by renaming column however beleive fields are stored named so changing the name might not be so straight forward, that route might require maming new column and quick script to copy existing over to new field without the .

[mohammed9q9]

When i rename the custom field, all data is lost.
Could you please elaborate on the copying to new field.
i create a new field Road and then copy the contents ?
Kindly advise on how to copy and if it needs to be done while the work period is closed or i can do it while the pos is being used to take orders ?

[Memo]

The entity custom data is stored as JSON. So when you change your field, you’re only telling SambaPOS to look for a certain field when the data is deserialised.

What JTR is talking about wrt a script, would be parsing the JSON in SQL Server and changing the name of the field.

To be honest, I find working with JSON in the database to be a huge pain in the ass. It would probably be a lot easier to just loop through the entities in python, php, or even C# and update what’s needed and then write back to the database.

[Jesse]

You need to escape the . In the name but the only way to do that is with a script that feeds that specific field in the report tag. I’m not even sure if it would work properly because you would have to nest a script tag inside a report tag for a field.

[Memo]

I whipped up a little utility to change the field name. In SambaPOS, set the field name back to its original name if you changed is so it will match data currently in the database.

Perform a backup.

Launch application, select entity type, and the field you want to rename, enter new field name and click “Update”. Update your entity display format afterwards to new name.

Entity custom data, ticket entities, and entity custom field will be updated.

Binaries and source code included.

EntityFieldChanger.zip (300.8 KB)

EDIT
updated files

[JTRTech]

I would have done a samba script to loop entities and copy between fields but either way

[Memo]

That’s all it’s doing and I don’t have to use JavaScript. Though I do like the dynamic nature of working with JSON that JavaScript has.

[mohammed9q9]

@Memo Thank you, let me try this

[mohammed9q9]

@Memo does the utility require any permissions to be granted to SQL Server.
Also kindly advise if its better to be done when the work period is closed ?
sorry for all the questions, a newbie here.

[Memo]

No need to apologise!

The utility doesn’t require any special permissions. It will work on any terminal that is configured to use SambaPOS as it will get the databsae connection string directly from SambaPOS’ configuration.

I forgot to update ticket entities in the last build. Below is an updated version.

EntityFieldChanger.zip (300.8 KB)

[mohammed9q9]

Dear @Memo
I am getting the below error when launching the app

[mohammed9q9]

Dear @Memo

The log file is as below

2022-04-04 09:33:25.972 [FATAL] UNHANDLED WINFORMS EXCEPTION: - [System.Data.SqlClient.SqlException]
System.Data.SqlClient.SqlException (0x80131904): Invalid object name ‘dbo.EntityTypes’.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod, Boolean isInternal)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task1 promise, Boolean requiresSynchronization)
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at EntityFieldChanger.SambaData.d__2.MoveNext() in C:\Users\wtb\source\repos\EntityFieldChanger\EntityFieldChanger\SambaData.cs:line 99
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at EntityFieldChanger.Form1.<Form1_Load>d__3.MoveNext() in C:\Users\wtb\source\repos\EntityFieldChanger\EntityFieldChanger\Form1.cs:line 28
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.<>c.b__6_0(Object state)
ClientConnectionId:6560e1de-a6fc-4862-9d1f-a5ce8e46538a
Error Number:208,State:1,Class:16