How do I go about viewing my database in a program so that I can modify a lot of stuff.
Ive chose Microsft server 2014 and dont know where the databsae file is stored, or what program I need to view the databse?
1 Like
Microsoft SQL Server Managememt Studio
MSSMS for short. Lots of discussions about it in this very forum. Use search function and you can get more details.
Modifying the database directly is not good idea you will certainly break something or harm the integrity of your system.
If you share more of what you want to modify and why we may be able to help you.
I have over 2.5k list o members, each wtih emails, id nubmers age and cant work out how to batch import it all
Be sure to backup before attempting anything…
That your not sure how to do obviously increases the chances of messing it up 
We have API support. We could create something that does this.
1 Like
Good idea Kendash. That would definitely work.
What is the format of the list file?
Its in an excel file
Ok, so an excel file can be saved as CSV.
You can use JScript in the Automation > Scripts section.
You will use the file.X() Helper to read and parse the file:
file.ReadFromFile(filePath)
And the API to create Entities… https://sambapos.com/wiki/doku.php/jscript_api
More specifically, you would be using the following, after setting up the Entity Type:
api.Entity(name).Exists();
api.Entity(name).Create(entityTypeName);
api.Entity(name).Data(name).Update(value);
api.Entity(name).Data(name).Get();
Ok thanks for that. a couple of questions,
What do I use for the file path? like where is it from.
And on those api commands Im a bit unsure about how to use them, I dont see where I can set the field Im updating the value for and I just loop through the file for each entry right?
Set up your Entity Type to contain the Data Fields you require.
Read the file and parse it to populate variables (I am using the f prefix to distinguish these variables as having been populated by reading from a file):
var fName;
var fMemberId;
var fEmail;
var fAge;
file.ReadFromFile('D:/Programs/POS/members.csv');
Depending on what you choose as Primary Field Name, check if the Entity exists. Use 1 of the following:
api.Entity(fName).Exists();
api.Entity(fMemberId).Exists();
Create the Entity if it does not exist. Again, it depends on your Primary Field. Use 1 of the following:
api.Entity(fName).Create('Members');
api.Entity(fMemberId).Create('Members');
Update the Entity with the rest of the Data.
If Name is the Primary Field:
api.Entity(fName).Data('MemberId').Update(fMemberId);
api.Entity(fName).Data('Email').Update(fEmail);
api.Entity(fName).Data('Age').Update(fAge);
If MemberId is the Primary Field:
api.Entity(fMemberId).Data('Name').Update(fName);
api.Entity(fMemberId).Data('Email').Update(fEmail);
api.Entity(fMemberId).Data('Age').Update(fAge);
Im not at all confident with javascript sorry, are you abele to help me out a bit more, as in populating the var’s and looping though each method for every line
###members.csv
Name,Phone,Email,Age,MemberId
John Doe,555-1234,,25,333444555
Mary Jane,555-5678,,32,777888999
This script uses the following assumptions about the CSV file:
- first line is field headers
- header field names match Custom Data Field Names in the Entity Type definition
- first field is the Primary Field
##EntityFunctions
[ent](Script)##
| Script Name: | EntityFunctions |
| Script Handler: | ent |
Script:
function importEntities() {
var entityType = 'Members';
var fLine;
var fields;
var field;
var created = 0;
var updated = 0;
// read the file
var fContent = file.ReadFromFile('D:/Programs/POS/members.csv');
// create array of file lines
var fLines = fContent.split("\r\n");
var lineCount = fLines.length;
// first line is header field names ... Name,Phone,Email,Age,MemberId
var fHeaderFields = fLines[0];
var fHeaderField = fHeaderFields.split(",");
// loop through lines
for (var l=1; l<lineCount; l++) {
if (fLines[l] != "") {
fLine = fLines[l];
// array of line fields
fields = fLine.split(",");
fieldCount = fields.length;
// loop through fields
for (var f=0; f<fieldCount; f++) {
// if (fields[f] != "") {
field = fields[f];
if (f==0) { // first field is Primary Field
fPrimary = fields[f];
// create Entity if it does not exist
if (!api.Entity(fPrimary).Exists()) {
api.Entity(fPrimary).Create(entityType);
created++;
} else {
updated++;
}
} else { // other fields are Data Fields
fData = fields[f];
api.Entity(fPrimary).Data(fHeaderField[f]).Update(fData);
// dlg.ShowMessage("Updated "+entityType+" Entity:\r"+fHeaderField[0]+": "+fPrimary+"\r"+fHeaderField[f]+": "+fData);
}
// } // if field not empty
} // field loop
} // if line not empty
} // line loop
return 'Lines:'+lineCount+' Fields:'+fieldCount+"\r\nCreated:"+created+" Updated:"+updated;
}
3 Likes
Thankyou soo soo much!
1 Like
Just of curioisty, why does the script like have that black name? Is it a different version of smba?
The screenshot of the Script is produced by a program that I call Tutorial Builder Helper (TBH).
TBH is a tool I designed using PHP, JS, and SQL to read configuration data directly from the Database and produce screenshots and formatted data/text suitable for posting to the forum to help with efficiency of producing Tutorials and answers to questions.
2 Likes
EDIT: Im really quite stupid, whilst I did change the EntityType in the script and saved it, Samba never actually saved it so the program kept on crashing. Sorry
I finally got time to use this and I got an error 
-----------------------------
[General Info]
Application: SambaPOS
Version: 5.1.58
Region: en
DB: SQ
Machine:
User:
Date: 12/03/2016
Time: 6:57 AM
User Explanation:
-----------------------------
[Exception Info 1]
Top-level Exception
Type: System.Reflection.TargetInvocationException
Message: Exception has been thrown by the target of an invocation.
Source: mscorlib
Stack Trace: at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
at System.Windows.Threading.DispatcherOperation.InvokeImpl()
at System.Windows.Threading.DispatcherOperation.InvokeInSecurityContext(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Threading.DispatcherOperation.Invoke()
at System.Windows.Threading.Dispatcher.ProcessQueue()
at System.Windows.Threading.Dispatcher.WndProcHook(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
at MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)
at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)
at System.Windows.Threading.Dispatcher.PushFrame(DispatcherFrame frame)
at System.Windows.Threading.DispatcherOperation.Wait(TimeSpan timeout)
at System.Windows.Threading.Dispatcher.InvokeImpl(DispatcherOperation operation, CancellationToken cancellationToken, TimeSpan timeout)
at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
at Samba.Presentation.Services.Common.ExtensionMethods.PublishEvent[TEventSubject](TEventSubject eventArgs, String eventTopic, Boolean wait)
at Samba.Presentation.Services.Implementations.AutomationModule.NotificationClient.<>c.<NotifyEvent>b__3_0(ActionData x)
at Samba.Services.Implementations.AutomationModule.ActionDataBuilder.InvokeFor(Action`1 dataAction)
at CallSite.Target(Closure , CallSite , Object , Action`1 )
at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid2[T0,T1](CallSite site, T0 arg0, T1 arg1)
at Samba.Services.Implementations.AutomationModule.RuleExecutor.ExecuteWithoutLogging(Object dataParameter, Action`1 dataAction)
at Samba.Services.Implementations.AutomationModule.RuleExecutor.ExecuteWith(Object dataParameter, Action`1 dataAction)
at Samba.Services.Implementations.AutomationModule.NotificationService.NotifyEvent(String eventName, Object dataParameter, Int32 terminalId, Int32 departmentId, Int32 userRoleId, Int32 ticketTypeId, Action`1 dataAction)
at Samba.Presentation.Services.Implementations.AutomationModule.NotificationClient.NotifyEvent(String eventName, Object dataObject)
at CallSite.Target(Closure , CallSite , INotificationClient , String , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid3[T0,T1,T2](CallSite site, T0 arg0, T1 arg1, T2 arg2)
at Samba.Modules.PosModule.TicketViewModel.ExecuteAutomationCommand(String automationCommandName, String automationCommandValue, String nextCommandValue, Object dataObject, Boolean executeOnce, Boolean clearSelection)
at Samba.Modules.PosModule.TicketViewModel.ExecuteAutomationCommand(AutomationCommand automationCommand, Object dataObject, String selectedValue, String nextValue)
at Samba.Modules.PosModule.TicketViewModel.OnExecuteAutomationCommand(CommandContainerButton obj)
at Microsoft.Practices.Prism.Commands.DelegateCommand`1.<>c__DisplayClass6.<.ctor>b__2(Object o)
at Microsoft.Practices.Prism.Commands.DelegateCommandBase.Execute(Object parameter)
at Microsoft.Practices.Prism.Commands.DelegateCommandBase.System.Windows.Input.ICommand.Execute(Object parameter)
at MS.Internal.Commands.CommandHelpers.CriticalExecuteCommandSource(ICommandSource commandSource, Boolean userInitiated)
at System.Windows.Controls.Primitives.ButtonBase.OnClick()
at System.Windows.Controls.Primitives.ToggleButton.OnClick()
at System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(MouseButtonEventArgs e)
at System.Windows.UIElement.OnMouseLeftButtonUpThunk(Object sender, MouseButtonEventArgs e)
at System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(Delegate genericHandler, Object genericTarget)
at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
at System.Windows.UIElement.ReRaiseEventAs(DependencyObject sender, RoutedEventArgs args, RoutedEvent newEvent)
at System.Windows.UIElement.OnMouseUpThunk(Object sender, MouseButtonEventArgs e)
at System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(Delegate genericHandler, Object genericTarget)
at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
at System.Windows.RoutedEventHandlerInfo.InvokeHandler(Object target, RoutedEventArgs routedEventArgs)
at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)
at System.Windows.UIElement.RaiseTrustedEvent(RoutedEventArgs args)
at System.Windows.Input.InputManager.ProcessStagingArea()
at System.Windows.Input.InputManager.ProcessInput(InputEventArgs input)
at System.Windows.Input.InputProviderSite.ReportInput(InputReport inputReport)
at System.Windows.Interop.HwndMouseInputProvider.ReportInput(IntPtr hwnd, InputMode mode, Int32 timestamp, RawMouseActions actions, Int32 x, Int32 y, Int32 wheel)
at System.Windows.Interop.HwndMouseInputProvider.FilterMessage(IntPtr hwnd, WindowMessage msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at System.Windows.Interop.HwndSource.InputFilterMessage(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
at MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)
at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)
at System.Windows.Threading.Dispatcher.PushFrame(DispatcherFrame frame)
at System.Windows.Application.RunDispatcher(Object ignore)
at System.Windows.Application.RunInternal(Window window)
at System.Windows.Application.Run(Window window)
at Samba.Presentation.App.Main()
Inner Exception 1
Type: System.IndexOutOfRangeException
Message: Index was outside the bounds of the array.
Source: Samba.Services
Stack Trace: at Samba.Services.Implementations.ExpressionModule.ExpressionService.InvokeScript(String handlerFunction, Object dataObject)
at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)
at Samba.Modules.AutomationModule.ActionProcessors.ExecuteScript.Process(ActionData actionData)
at Samba.Services.Common.RuleActionTypeRegistry.ProcessAction(String actionType, ActionData actionData)
at Samba.Services.Implementations.AutomationModule.AutomationService.ProcessAction(String actionType, ActionData actionData)
at Samba.Modules.AutomationModule.AutomationModule.<OnInitialization>b__5_0(EventParameters`1 x)
at Microsoft.Practices.Prism.Events.EventSubscription`1.InvokeAction(Action`1 action, TPayload argument)
at Microsoft.Practices.Prism.Events.EventSubscription`1.<>c__DisplayClass2.<GetExecutionStrategy>b__0(Object[] arguments)
at Microsoft.Practices.Prism.Events.EventBase.InternalPublish(Object[] arguments)
at Microsoft.Practices.Prism.Events.CompositePresentationEvent`1.Publish(TPayload payload)
at Samba.Presentation.Services.Common.ExtensionMethods.Publish[TEventsubject](TEventsubject eventArgs, String eventTopic, Action expectedAction)
-----------------------------
[Assembly Info]
mscorlib, Version=4.0.0.0
DevExpress.Xpf.LayoutControl.v14.1, Version=14.1.11.0
System.Xml, Version=4.0.0.0
DevExpress.Xpf.Grid.v14.1, Version=14.1.11.0
System, Version=4.0.0.0
DevExpress.Xpf.Grid.v14.1.Core, Version=14.1.11.0
WindowsBase, Version=4.0.0.0
System.Xaml, Version=4.0.0.0
Samba.Domain, Version=1.0.0.0
System.Core, Version=4.0.0.0
PresentationFramework, Version=4.0.0.0
Samba.Infrastructure, Version=1.0.0.0
Microsoft.Practices.Prism, Version=4.0.0.0
System.Runtime.Serialization, Version=4.0.0.0
Microsoft.Practices.Prism.MefExtensions, Version=4.0.0.0
System.ComponentModel.Composition, Version=4.0.0.0
PresentationCore, Version=4.0.0.0
DevExpress.Xpf.Core.v14.1, Version=14.1.11.0
Samba.Services, Version=1.0.0.0
Samba.Presentation.Services, Version=1.0.0.0
System.Windows.Forms, Version=4.0.0.0
System.Drawing, Version=4.0.0.0
Stateless, Version=1.0.0.0
Samba.Persistance, Version=1.0.0.0
PropertyTools, Version=2012.4.14.1
Samba.Localization, Version=1.0.0.0
ReachFramework, Version=4.0.0.0
Samba.Infrastructure.Data, Version=1.0.0.0
EntityFramework, Version=6.0.0.0
FluentValidation, Version=3.4.0.0
Omu.ValueInjecter, Version=2.3.0.0
Microsoft.Practices.ServiceLocation, Version=1.0.0.0
Microsoft.CSharp, Version=4.0.0.0
-----------------------------
[System Info]
Operating System
-Microsoft Windows 8.1 Enterprise
--CodeSet = 1252
--CSDVersion =
--CurrentTimeZone = 600
--FreePhysicalMemory = 1002524
--OSArchitecture = 64-bit
--OSLanguage = 1033
--ServicePackMajorVersion = 0
--ServicePackMinorVersion = 0
--Version = 6.3.9600
Machine
-
--Manufacturer = TOSHIBA
--Model = PORTEGE Z10t-A
--TotalPhysicalMemory = 4216651776
--UserName =
-----------------------------
What is your Entity Type Name? The script assumes it is ‘Members’.
Were any Entities imported? If so, where did it stop?
Chances are, it is an issue with the CSV data that needs to be fixed. The script has no error checking to account for “bad” data.
I added an edit to my orignal post sorry, Samba didnt save the script when I changed the Entity Type Name, or I just stuffed up.
Any idea how to update member data?