Looking to build a customer database and CRM

I’m so very new to Codea, Lua and coding in general.

I’m looking to build a customer relation manager.

Goal is to have the app include
Create categories for contacts by customer type
Collect contact information for a contact card
Have the contact card list types of purchases and history of purchases
Have the database sort by last time I dealt with customer, last time the customer purchased from me.
Eventually have it connect with email so I can sent clients invoices or renewal notices

Basically I want to create a custom CRM like hubspot or salesforce that would be able to connect to email service to attach emails to clients profiles, add new contacts through web contact forms, and be able to send emails to clients I have heard from in a while and or send out newsletters and promotions.

Future goal would be to have the app work as a point of services system (cash register) so I can track sales at a till.

I would like to have it compatible on HTML, WordPress, macOS, windows, Linux, ios and android, which I’m sure is possible considering lua can be cross compatible.

I am not looking for someone to help me build, I am looking for direction on where, or how to start this.

I can find templates in lua online for aspects of what I want to build, but pulling them into codea is not staging forward, I’ve tried pulling a few projects into codea from GitHub for game or app templates. But most don’t import for what ever reason if they were built in lua, and not codea

Any help much appreciated!!

Thanks,
Travisbevan.com

@TravisBevan Codea is more for creating games then business applications. I’m not saying it can’t be done, it’s just going to be a lot of work. Codea doesn’t have database code, but that doesn’t mean you can’t write your own.

Thanks for the input @dave1707 , I am still trying to figure what would be best to build it in, even if I use Lua as a interface and run the database in something else.

@TravisBevan - there is a lua based sql lite which would allow you to interrogate a database. Search the forum for sql, I am sure someone has used this. It would need interfacing.

http://lua.sqlite.org/index.cgi/index

@TravisBevan Just in case you’re still looking for database code, here’s a limited example I have. This will give you an idea of the amount of code that’s required for something simple. There aren’t any edit checks, so it might crash at times. To input something, just tap on a field. Tap on Save to save the info. Tap Read to look at the entries. Tap Delete to delete an entry. The info is saved as a text file in the Dropbox folder. You can change the code to add or delete the number of input fields or change their names.

displayMode(FULLSCREEN)

function setup()
    fileName="Dropbox:tab1"
    rectMode(CENTER)
    dtab={}
    
    -- input tabs
    table.insert(dtab,input("i",WIDTH/2,HEIGHT-100,300,40,"Name"))    
    table.insert(dtab,input("i",WIDTH/2,HEIGHT-150,300,40,"Address"))
    table.insert(dtab,input("i",WIDTH/2,HEIGHT-200,300,40,"City"))
    table.insert(dtab,input("i",WIDTH/2,HEIGHT-250,300,40,"State"))    
    table.insert(dtab,input("i",WIDTH/2,HEIGHT-300,300,40,"Phone"))    
    table.insert(dtab,input("i",WIDTH/2,HEIGHT-350,300,40,"Email"))   
    table.insert(dtab,input("i",WIDTH/2,HEIGHT-400,300,40,"Misc"))   
    
    -- show tabs
    table.insert(dtab,input("s",120,HEIGHT-50,130,40,"Rec #",show))  
     
    -- functions tabs
    table.insert(dtab,input("f",WIDTH-500,HEIGHT-50,70,40,"Read",read))   
    table.insert(dtab,input("f",WIDTH-400,HEIGHT-50,70,40,"Save",save)) 
    table.insert(dtab,input("f",WIDTH-300,HEIGHT-50,70,40,"New",new))      
    table.insert(dtab,input("f",WIDTH-200,HEIGHT-50,70,40,"Delete",delete))  
    
    showKeyboard() 
    cnt=0 
    val=readText(fileName)
    if val==nil then
        qtab={}
    else
        qtab=json.decode(val)
    end
end

function draw()
    background(40, 40, 50)
    for d=1,#dtab do
        dtab[d]:draw()
    end
end

function keyboard(k)
    for d=1,#dtab do
        dtab[d]:keyboard(k)
    end
end

function touched(t)
    if not isKeyboardShowing() then
        showKeyboard()
    end
    for d=1,#dtab do
        dtab[d]:touched(t)
    end    
end

function read()
    for z=1,#dtab do
        dtab[z].str=""
    end
    cnt=cnt+1
    if cnt>#qtab then
        cnt=0
    else
        for z=1,#dtab do
            dtab[z].str=qtab[cnt][z]
        end
    end
end

function save()
    cnt=0
    f=#qtab+1
    qtab[f]={}
    for z=1,#dtab do
        if dtab[z].type=="i" then
            qtab[f][z]=dtab[z].str  
            dtab[z].str=""   
        end                   
    end
    val=json.encode(qtab)
    saveText(fileName,val)
end

function delete()
    table.remove(qtab,cnt)
    for z=1,#dtab do
        dtab[z].str=""
    end
    val=json.encode(qtab)
    saveText(fileName,val)
    cnt=0
end

function update()
    for z=1,#dtab do
        qtab[cnt][z]=dtab[z].str  
        dtab[z].str=""                      
    end
    val=json.encode(qtab)
    saveText(fileName,val)
    cnt=0
end

function new()
    for z=1,#dtab do
        dtab[z].str=""
    end
    cnt=0
end

function show(self)
    fill(255)
    str=self.name.."  "..cnt.." of "..#qtab
    text(str,self.x,self.y)
end

input = class()

function input:init(t,x,y,w,h,n,f)
    self.type=t
    self.x=x 
    self.y=y 
    self.width=w 
    self.height=h 
    self.name=n
    self.left=x-self.width/2 
    self.right=x+self.width/2;
    self.bottom=y-self.height/2 
    self.top=y+self.height/2
    self.str="" 
    self.selected=false
    self.func=f or function() end
end

function input:draw()
    strokeWidth(0)
    local cur=""
    if self.selected then
        stroke(255,0,0)
        strokeWidth(4)
        cur="_"
    end
    fill(123, 185, 215, 255)   
    rect(self.x,self.y,self.width,self.height)
    if self.type=="s" then
        self.func(self)
    elseif self.str~="" and self.str~=nil then
        fill(10, 9, 9, 255)
        text(self.str..cur,self.x+2,self.y)
    else
        fill(255)
        text(self.name,self.x,self.y)
    end
end

function input:touched(t)
    if t.state==BEGAN then
        if t.x>self.left and t.x<self.right and
                t.y>self.bottom and t.y<self.top then
            for c=1,#dtab do
                dtab[c].selected=false
            end 
            if self.type=="i" then
                self.selected=true
                self.str="" 
            elseif self.type=="f" then 
                self.func()
            end
        end
    end
end

function input:keyboard(k)
    if self.selected then
        if k==RETURN then
            self.selected=false
        elseif k==BACKSPACE then
            self.str=string.sub(self.str,1,#self.str-1)
        else
            self.str=self.str..k
        end
    end
end

You could totaly do this with Codea. BUT

In your case, I would first create a web application with, say e.g., php for the server side and html / css / javascript for the client side. If you then still need a native iOS app for whatever reason, you could still extend the server with REST API’s. These are simply URL’s that you can call as an admin to e.g. create new users and save and read other information. In Codea you could then create an user-friendly interface that simply calls these REST endpoints when you interact with the app.

This is a veeery broad topic actually.

However, you could also create everything in Lua instead if you prefer, using a Linux Server and Codea. You could even use MySQL as your database and talk directly to it through Codea (instead of using the REST API). I published an adapter for this some time ago

But honestly speaking, I’d never use a native app for something like your idea. Because HTML and JavaScript is future. And the more cross-platform you want to be, the more you should think about web technologies.