Possible to build a Excel-like Table

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Hans-Helmut
Posts: 57
Joined: Sat Jan 14, 2017 6:44 pm

Possible to build a Excel-like Table

Post by Hans-Helmut » Wed Jan 18, 2017 4:48 pm

I am wondering if this is possible and I tried to get it working to some smaller degree.

The usual field can be table field with columns and rows and a grid can show their position.
Now I would like to enter and manage data as in an Excel spreadsheet - or at least kind of doing this.

Trying this and that and trying to study the dictionary, and some messages here, I found that a lot depends on how keyboard entries are detected and then used inside the field.

I am working on Windows and I do not know anybody who works with Apple machines on the. So, priority is Windows in my environment, and I want to eventually have this also done for other users if LC allows me.

Is there anybody who can help?

What did I do so far? I want to enter a field but the "cell edit" that is provided does not work well, and it is not what I want.

It should be possible to:

1. Enter any "field" or cell of the spreadsheet at any time, and this entry must visually represent itself.
2. Using shiftkey-tab I want to move from cell to cell backwards. Tabbing moves forwards, return keys move to the next row in the same column (or beginning of the next row), and shift-return goes up to the previous row.
3. The deletion of data using the back key must not delete anything that is not in the cell itself. So, it does not delete tab keys or returns.
4. Hitting control-return will create a new row if there is not a grid with any number of rows already.
5. Depending on the type of data (text, dates, numbers, decimals) the column will format itself, or an individual field is formatted.
6. It would be ideal if columns and rows can be hilited as a row or column. Also, more than one column or row should be hilited. Also - if possible - discontinuous parts of the spreadsheet. This is not first priority, but a "nice to have". And selected areas should allow for certain operations: changing columns, deleting columns, adding columns, formatting columns, etc.
8. I would like to have a cell fully hilited with whatever color is chosen, not just the text string inside of it. Currently this does not look idea if the text inside a cell is hilited. I do not know how to accomplish this yet using the formatting capabilities for table-like fields.
8. There could eventually be a mode to edit formulas, but users are not using this even in Excel and it is not needed.

The main purpose is for data entry. People like to enter visually into a list directly into the new row after the last row, or into the first row before all other rows. And I like this too.

To some degree, I get control over list cells detecting rawkey and key messages (on rawkeyup, on rawkeydown and keydown and - up messages) and preventing deletion of characters that should not be deleted. But it does not "feel" as slick as it should be. I do not know what would be the best for really controlling the behavior to its full extend.

For example, I came up with:

on rawkeydown myKey
if myKey is 65289 then -- tab key
put tab after me
else if myKey = 65288 -- back key
delete last char of me
else
pass rawkeydown
end if
end rawkeydown

But I could not yet figure out how to prevent the deletion of tabs and returns. And also I could not yet find a way of using shift-tab while also preventing undesired deletions.

Is there any documentation about how to work with all available functions and properties regarding fields and text - not just to have to look through the dictionary to find out and do a lot of trial and error?

This would also include the visual properties such as margins, lineheight etc. There is something, but I do not fully understand all the possibilities yet.

Then maybe someone has already done this?

Another way I was thinking - but I am not sure about it. If I forget about any tables and lists (not speaking of the datagrid anyway), would it make sense using individual feels which are put together as rows, and each row could be a group for example.

Then I can clone the row group and add it to the bottom of the previous row. Or i can create a number of rows which then look like a spreadsheet and have people enter into these pseudo cells. And users can add more such rows as they like.

The advantage would be that cells are actually fields, and it is probably easier to manage everything.

What I did was some tests: Each field has a cell name such as "F_1_1", "F_1_2" etc. So the first item indicates the position in a row, the second item the position of the column. Then I can use variables. Let us say, I have a variable tRow, and a variable tCol. It does not work using "F_" & tRow &"_"& tCol directly addressing such one field. But if I put it the following way:

Code snippet:

put "F_" &tRow& "_" &tCol into fName
put "Hello" into field fName

This works really nicely.

And I can change the properties such as alignment, color, width, etc.

So, using a repeat loop, I can change the size of one column with all fields of that column and even make it be another column, swapping columns, creating new ones, deleting columns, inserting them, reformatting them, etc.

-- tRow = number of rows
-- tCol = number of columns

repeat with i = 1 to tRow
repeat with j = 1 to tCol
put "F_" & tRow & "_" & tCol into fName
-- do something with field f Name
end repeat
end repeat

Well, I imagine this leads to quite many functions, especially if this should work based on what the user is doing with each action recalculating everything.

For some rows and columns this might be fine, but what about data with 100, or 1,000 or 10,000 rows? I think then it will become too slow.

Maybe I am thinking in the wrong direction? I would like to be corrected here if this is the case.

I looked at the Datagrid, but I can not see (yet) how to customize it to have such functionality as I would like to have. And it uses arrays which I do not master yet in LC. But maybe I should consider them rather than possibly reinventing the wheel? Datagrid is a bit confusing to me. And I imagine I must spend at least a weekend to understand parts of it.

Any direction is HIGHLY welcome, please...

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Possible to build a Excel-like Table

Post by MaxV » Wed Jan 18, 2017 6:29 pm

Just create a group of fields, then use this code to create new fields:

Code: Select all

create field
set the name of the last field to myCounter
then use behaviours ( http://livecode.wikia.com/wiki/Behavior ) to set the same scripts for all old and new fields.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Hans-Helmut
Posts: 57
Joined: Sat Jan 14, 2017 6:44 pm

Re: Possible to build a Excel-like Table

Post by Hans-Helmut » Wed Jan 18, 2017 11:22 pm

well, yeah, behaviors are another level of abstraction. I looked into the link you gave. I will make some testing with this.

What I did not first understand is your code snippet:

Code: Select all

create field
set the name of the last field to myCounter
If this means that myCounter contains this name made by the counter variables (in my case i and j) then yes, this is what I was doing. First, I misunderstood your phrase "myCounter" to be just a serial number.

I use something like this:

Code: Select all

constant kLeft=300 , kTop=200, kHeight=22, kWidth=100

on mouseUp
   createFieldTable
   if there is a field "F_1_1" then
      put "Success" into field "F_1_1"
      select after line 1 of field "F_1_1"
   end if
end mouseUp

/* Example snippet to create a table of adjunctant fields
using rows and columns
allowing users to enter data in a table-like style */

command createFieldTable
   lock screen /* If this is not done it will be awfully slow */
   
   /* Creating local variables which will have changing values */
   put kLeft into tLeft
   put kTop into tTop
   put kHeight into tHeight
   put kWidth into tWidth
   
   /* The actual loop creating fields */
   put 0 into i /* Lcal counter for lines */
   put 0 into j /* Lcal counter for columns */
   repeat with i = 1 to 3 /* This would create 3 rows */
      set the cursor to busy
      repeat with j = 1 to 4 /* This will create 4 columns */
         put "F_"& i &"_"&j into fName /* This is a dynamically created field name */
         if there is a field fName then
            delete field fName /* Deleting first to re-creating for this test only */
         end if
         create field
         set the name of last field to fName
         set the height of field fName to tHeight
         set the width of field fName to tWidth
         set the left of field fName to tLeft
         set the top of field fName to tTop
         add tWidth-2 to tLeft /* For the default field style this creates visually adjuncted lines between fields */
      end repeat
      put kLeft into tLeft /* Resetting the left starting point */
      add tHeight -2 to tTop /* Same as for width, subtracting 2 pixels */
   end repeat
end createFieldTable

The question was if this is good practice or even best practice. But maybe it is really not so bad because using fields as cells of a table like structure will allow to address each cell = field individually and define it the way needed for an application.

If this is done inside an LC group then it can even be scrollable and new lines of fields could appear as the user is scrolling. As I read something about the datagrid, they seem to use not so many rows of fields, just enough to be visible on screen, and data is made visible in existing fields changing exchanging data. Or at least, this is what I thought it was done.

But to get dynamic behavior and the needed level of abstraction, I think it is quite a big task to do -- at least for me. Learning... )

That is why I was wondering if this was already done by someone to not reinvent the wheel.

(I am not writing all this just for myself. I just try to imagine that other people might have similar thoughts and may wish to share, especially when rather new to LC.)

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Possible to build a Excel-like Table

Post by MaxV » Thu Jan 19, 2017 10:21 am

Datagrids are not very flexible, so for your task a group of field probably is better.
I suggest you also something like:

Code: Select all

create field in group "MyTable"
the Group "myTable" can have its script, so you have many ways to override some commands with other.
Moreover you can put fields one near another with:

Code: Select all

set the left of field ("F_"& i &"_"&j) to the right of field ("F_"& i &"_"& (j - 1)) 
and so on.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Possible to build a Excel-like Table

Post by MaxV » Thu Jan 19, 2017 10:25 am

Regarding the amount of fields on the screen, it's a simple trick.
Just put 2 more lines over the visible (one in the top and one below the visible rect of the group), then when you scroll the table (group), you delete and create new lines.
The optical effect is an unlimited number of rows, but there are just 2 more of the visible rows.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Hans-Helmut
Posts: 57
Joined: Sat Jan 14, 2017 6:44 pm

Re: Possible to build a Excel-like Table

Post by Hans-Helmut » Thu Jan 19, 2017 10:19 pm

NICE INPUT. THANK YOU ..!

Regarding creating fields in a scrolling group as an illusion that there is an endless number of fields, I have questions -- because I want to implement this.

So, let us imagine 5 fields within a group. 3 of these fields are visible. 2 of them are only partially visible.

The user starts scrolling. He might scroll slowly or very fast, up or down. On a mobile, he will use such scrolling as well.

How to get this vScroll into synchrony with data being displayed?

Data would reside either in an array or in a delimited text list. I did some testing with data in arrays and in simple text lists with tab delimited data. My tests with version 8.1.2 came to the conclusion that tabbed lists seem to be faster. But I am not showing millions of rows (so far))). Probably then arrays must be the choice.

So, somehow the vScroll value is related to the position in the list - whatever it is. vScoll = 0 will show the records on the top of the list (in this case 5 rows). When the vScoll is maximum then the end of the list is reached. Any other chunk of rows must be calculated based on the vScroll value. Correct?

At the same time, there must be a hidden linking value allowing us to know that a visible row of data is related to a row of the array in memory. So, the user, clicking on such row or any field in such row, will trigger whatever it is in the corresponding data kept in memory.

Then my thinking of creating an Excel-like spreadsheet with LC is that there is not just one table group, but each row and each column should be a group as well? It might then be faster to simply add such groups as rows or columns ...

Now, what would we do for the following use cases:

- Rows are hilited
- Columns are hilited
- Column is swapped with another column
- Column is deleted
- New Column is created
- Width or height of a column is changed
. etc.

Well, it seems all could be done, except that I do not yet know how to hilite a full column or row the way Excel is doing it. As far as I understand, it is not possible to define color, width, etc of border parts of fields or buttons. I can not change the thickness of just the left border side and thereby create a visual effect?

So, the effect of hiliting a group of fields would need an object (a rectangle for example) that would be behind transparent fields, or create the illusion of a hilited area, row, column or any other area the user selected.

All this looks quite challenging to do.

So, whatever data is displayed in fields, such data must contain some id value and set a custom value in the visible field object that allows to related visible data in fields to the actual data in memory. The user, changing values in the fields, would then change the value in the data structure in memory which then would be saved to either a database or to a temporary file.

Values could be formulas as in Excel, or actual data, or calculated values, and would be displayed as formatted values (text, dates, decimal strings, etc.)

But I imagine users would want to select not just one, but many fields together and copy, paste, change, delete values in a range of cells... How to do that? It sounds like some processing behind the scenes. And not all is imaginable yet in LC - or is it...?

How to update such calculations in real time? Will LC maintain a decent responsiveness and speed?

Well, I am intrigued nevertheless for such Mini-Excel, which then would be under our own full control.

Any helpful script snippets to solve one or the other problem in this?

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Possible to build a Excel-like Table

Post by MaxV » Fri Jan 20, 2017 2:17 pm

Hans-Helmut wrote: Regarding creating fields in a scrolling group as an illusion that there is an endless number of fields, I have questions -- because I want to implement this.
So, let us imagine 5 fields within a group. 3 of these fields are visible. 2 of them are only partially visible.
How to get this vScroll into synchrony with data being displayed?
with a fake new scrollbar at the right of the group, yuo can use the scrollDrag message. Just change EndValue property dynamically.
Hans-Helmut wrote: Now, what would we do for the following use cases:
- Rows are hilited
- Columns are hilited
- Column is swapped with another column
- Column is deleted
- New Column is created
- Width or height of a column is changed
. etc.

Well, it seems all could be done, except that I do not yet know how to hilite a full column or row the way Excel is doing it. As far as I understand, it is not possible to define color, width, etc of border parts of fields or buttons. I can not change the thickness of just the left border side and thereby create a visual effect?
you can do it, borderColor,backGroudColor, hiliteColor and so on ... these properties are what you need
Hans-Helmut wrote: So, whatever data is displayed in fields, such data must contain some id value and set a custom value in the visible field object that allows to related visible data in fields to the actual data in memory. The user, changing values in the fields, would then change the value in the data structure in memory which then would be saved to either a database or to a temporary file.
Values could be formulas as in Excel, or actual data, or calculated values, and would be displayed as formatted values (text, dates, decimal strings, etc.)
WOW, you have a huge project in mind, start at baby step, formulas can be very complicated.
Hans-Helmut wrote: But I imagine users would want to select not just one, but many fields together and copy, paste, change, delete values in a range of cells... How to do that? It sounds like some processing behind the scenes. And not all is imaginable yet in LC - or is it...?
You can do all in livecode, you can save data in a sqlite external file.
Hans-Helmut wrote: How to update such calculations in real time? Will LC maintain a decent responsiveness and speed?
It depends, the real trick is who will do the calculus? For example when I use sqlite, I tend to push on sqlite some calculations.
However you have to update just the filed seen by the user, so livecode is enough fast to do this. Keep in mind that you can also do fast videogames in livecode :D
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Hans-Helmut
Posts: 57
Joined: Sat Jan 14, 2017 6:44 pm

Re: Possible to build a Excel-like Table

Post by Hans-Helmut » Tue Jan 24, 2017 10:35 pm

Thank you for the nice replies.

Mini-Excel

Well, given the time, I will take over the challenge trying to make something like Excel. I am not sure how far I will get. It is an exercise for now. I am very grateful for helpful hints. And if I come up with something then I will share. Before paying for a company version, I need to learn more.

I am interested in professional development with a team for the professional market. I hope the product can supply on this. But to give it to others, I have to have a thorough knowledge myself first.

There are so many functions that I am sometimes losing the overview and searching around seems to take quite a bit of time.

Just as a question: Should such an Excel-like spreadsheet not be made using LCB? I have not been able to get down to that, but I wonder what it offers. If it is "just" an easier C++ then a hardcore developer will probably still prefer to code using their "native" language? I do not yet fully understand...

I was thinking what might NOT be possible. Well, probably everything is possible, but given the tools in Livecode, I still think that a hiliting an area made out of cells is possible, but not the same way it is done in Excel itself.

Best practice using databases?

Thank you also for hints regarding databases. Sure, data must be stored somewhere.

Over the weekend, I was studying SQLite - which seems to be very fast on the local machine. Somewhere someone wrote about to put SQLite into memory and run it from there. How is this done? I am trying to set up another layer to handle calls to and from this database in an easy way and it seems to work well. But it also takes some time to learn it all.

Then we (a programmer friend) made a test with PHP and Livecode. Actually, it worked using the POST command. We are going further this direction as well - learning. Also, it will take time.

What would be the best strategy synchronizing between SQLite and PHP/MySQL. Currently, we can not set up Livecode server. Are there some approved strategies for synchronization?

My idea is that the local app (mobile, desktop) only talks to the local database (SQLite) and then there is an automatic update process between SQLite and the server database. In most business applications there should be no conflict. People will usually not work on the same record at the same time. And this could be prevented.

Ideally of course, SQLIte and MySQL (or whatever other database management system) will synchronize without blocking the work of the user. Could this be another instance of Livecode started managing this separate connection?

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Possible to build a Excel-like Table

Post by AxWald » Wed Jan 25, 2017 9:13 am

Hi,

very interesting topic. Subscribed.

It's one of these points on my list that are meant to "soon come™" - for too long already. I don't feel comfortable with the dataGrid, it feels ways to complicated & sluggish, for the little benefit it has to offer - I see it as a nice design/ layout gadget with a main use case to confuse 'em newbies.
Table fields, on the other hand, are quite fast and reliable (as long as you omit the cREVTable & cell editing thing that will drive you crazy ...), but seriously lack formatting options and will quickly give you nasty problems beyond very basic layouts.

The main problem, IMHO, is that LC completely lacks a native data type for table-like data. "Look at the arrays!", I hear 'em saying. Well, I did. You can read about it here (that's just my summary; it took me quite some time to reach it in the posts before ...) - in this thread I learned that what we have is an associative array, i.e. key-value pairs only. Not real tables where you can easily access any given value in a notation like "C3", let alone absolute references like "$C$3", where you can sort/ filter, all that stuff that so many people are meant to do during their daily work hours ...

It might be possible to emulate such using arrays (heavy splitting/ joining/ sorting assumed), but I fear the result would soon become as sluggish as the dreaded dataGrid. It might, in the end, make more sense to create a plugin that implements OLE, so that we could use real Excel (or the highly recommendable "PlanMaker" from Softmaker Office; even LibreOffice is said to support it) "plugged in" into our LC programs.

This said, I'd rather recommend a "feature reduced approach".
What's it that we really need?
  • An "array of fields" that can be produced "on-the-fly", with individual formatting for each field.
  • This "object" may be a group containing its basic properties as custom Props, and will come to life with the data you feed it.
  • Headers (above) and maybe indices (left or right) should be possible (but could be implemented outside the "object").
  • Scripts should be possible for each field, each row, each column and the whole "object".
  • Sorting and filtering (= x, <> x, matches "x*") must work. Basic "*" matches are sufficient. This must work with right mouse/ long touch.
  • Addition for me only: Must work in Android, Windows, maybe Linux. This is where my food comes from.
Did I forget something? As mentioned, I'd recommend to keep the feature set as close as possible - later additions are possible in any case.

And since a "table-like object/ data type" is quite boring as long as you cannot treat it with SQL statements, I'd love to try to bind this "table-like object" directly to a SQLite db.
SQLite is lightning fast on today's machines, as long as the amount of data is within reasonable limits. Imagine that, as soon as you change a value in the field, an SQL update query is fired to the db - you actually work in the db table! Which cries for a separate "undo mechanism" - you see, it's becoming complicated very fast ;-)

So let's skip this above paragraph, for now. First things first. But the table thing above is in my pipeline, I'll start to work on it during the next weeks.


But Hans-Helmut also mentions something that I'm actually working on, having SQLite as a cache db for a mobile solution where 3G/4G isn't reliably available.
ATM I'm compiling the data I need on the road, creating tables in SQLite and stored procedures in MySQL to fill one db from the other.

I searched a bit (google "Replication MySQL SQLite") for existing solutions, but didn't find yet exactly what I looked for. "SQLite-Sync" hit my eyes (very badly, still bleeding!), they seem to have the same web designer as RunRev ;-)
"SymmetricDS" looks rather promising, comes in a GPL version, but is, as most other ready-mades, ways to oversized/ -featured.

So I'll roll my own. This gives me full control for anything happening, what, when, and why. I'll avoid the problem with concurring access with heavily restricted write access (the mobile client can only write things that no other process can possible have write access in this moment, or that can be queued), and I'll work with very limited subsets of the actual data to reduce bandwidth and data volume consumption. As soon as the initial rollout/ field test/ proof of concept is done (here there is no really sensible data) I'll add compression & encryption.


Regarding the (MySQL) databases on the servers, these are usually beyond my responsibility (managed by other company's, all offer direct access). This is not what I'd do, but all I can do is to keep the credentials well encrypted. I'd rather have nice per-user restrictions as you can have with MS SQL, but, well, it's MySQL & I have to live with it.

Regarding PHP - well, I'd not use it much myself. Isn't it another heavily used, bloated scripting language, usually rarely patched for compatibility reasons, intimately known by any script kiddy & its dog? I'd rather see it as another attack vector. Learning it sufficiently well myself is beyond the volumetric limitations of my brain, and relying on hired PHP devs? What I've seen 'til now done by PHPers made me pull quite some of my few remaining hair. It might be similar with them as with web designers - statistically there must be good ones somewhere out there, but obviously "hiding very well" is crucial part of their education ;-)

LC on the server would be rather tempting, but any tries yet to only get a "Hello world!" failed miserably. The LC server seems not to like the typical web space LAMP servers my customer prefer, not enough permissions to configure the Apache sufficiently? Or to poor & outdated documentation? Or to stupid & dumb the user (= me)? Or all together? Who knows.

Anyways. Time over. Must start coding again. Making more tables.

This is what I'm on these days. I'm happy to share my findings, all I write is GPL v3 anyways. Beware, it might poison your code! ;-)))

Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9802
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: Possible to build a Excel-like Table

Post by FourthWorld » Wed Jan 25, 2017 6:57 pm

AxWald wrote: I don't feel comfortable with the dataGrid, it feels ways to complicated & sluggish, for the little benefit it has to offer - I see it as a nice design/ layout gadget with a main use case to confuse 'em newbies.
Table fields, on the other hand, are quite fast and reliable (as long as you omit the cREVTable & cell editing thing that will drive you crazy ...), but seriously lack formatting options and will quickly give you nasty problems beyond very basic layouts.
Multi-column list fields are great for displaying lists. One object, easy to work with, and now that the LC field object includes a tabAlign property for independent alignment of columns there's no need to use the DataGrid for lists.

The DataGrid is great for form layouts, in which each cell needs more than just a delimited single-line display. It's among the most flexible tool I've ever seen for such tasks, quite handy when that's what you need.

A spreadsheet is a very different thing from a list, and in terms of workflows spreadsheet applications serve a fundamentally different role than databases.

While it's true that folks sometimes use spreadsheets as a sort of lightweight database, the two application categories are designed for very different tasks, and as such have very different UI and architectural requirements.

It's possible to make a spreadsheet in LC, but given how very specialized such a tool is, and how many great ones exist (LibreOffice Calc runs on everything and is completely free) I haven't seen much interest in replicating that from scratch in LC.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

Hans-Helmut
Posts: 57
Joined: Sat Jan 14, 2017 6:44 pm

Re: Possible to build a Excel-like Table

Post by Hans-Helmut » Thu Feb 09, 2017 1:49 pm

Richard, you are a Livecode Expert and I really appreciate the forum and your continued contributions here. For a "beginner" - well, not a bloody beginner any longer, but still a beginner - your directions are helpful, and of course, any suggestion is just a big huge help.

And I am thanking everybody for exchange thoughts so freely.

The logic here - and why I am thinking in the direction of Excel: My customers really love working with Excel, and much work is done using Excel even for writing invoices or doing any kind of calculations. The huge big drawback is that there is no work-flow and there is no real database. Also, it is too easy to enter data which is not checked and it happened more than once that people are entering wrong data, wrong calculations, copy and paste wrong rows or columns, etc.

So, the idea was to have a hybrid application - on one side it looks familiar to customers working with Excel - on the other side it provides the level of security and validity and data storage that only a tool such as LiveCode would provide.

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: Possible to build a Excel-like Table

Post by Mikey » Thu Feb 09, 2017 3:29 pm

The other thing you can do, that we have running 24/7, is have LC with various controls updating a DB, and also updating a remote spreadsheet, that is also acting like a DB. Users can pull data from the spreadsheet and other spreadsheets that they maintain, and build reports from all of it. LC then is able to grab the additional data from the users' other sheets, and incorporate that as needed (perhaps it pulls pricing data from a user's own sheet, then updates a third sheet with sales projections).

The data sources and repositories are then the db and the spreadsheets, and if you really want to get silly, you could also use LC's ability to store data in a stack as a data source, too.

Right now we're only using Google Sheets spreadsheets to do this, but we're going to be doing it with Excel spreadsheets as soon as I get the chance to fiddle.

I gotta tell you, this setup is badass. I have n00bs who are building custom reports from the spreadsheet data sources, and users who are walking around with ios devices doing their thing, and all these different pieces work great together.

Lagi Pittas
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 365
Joined: Mon Jun 10, 2013 1:32 pm

Re: Possible to build a Excel-like Table

Post by Lagi Pittas » Thu Feb 09, 2017 4:31 pm

Hi

Why not look at

Zyrip's Excel Library it's in Open Beta so you can have some input
http://www.aslugontheroad.com/in-progre ... r-livecode

Code: Select all

The Excel Library 1.3 for LiveCode extends the LiveCode capabilities by providing more than 200 commands or functions for controlling MS Excel with LiveCode.

The commands are grouped in categories:

Application
Window
Workbook
Sheet
Range
Column
Row
Cell
Pivot Table
Chart
Shape
Print Setup

Curry Kenworthy's SpreadLib is more for reading and writing as far as I can see and doesn't need Excel on the machine.

http://livecodeaddons.com/spreadlib/

There is also this one but it is a bit more expensive , looks to be like Zyrip's library with a cursory look
https://livecode.com/products/thirdpart ... ary-1-3-3/

With what you want to do I'd put both in my Arsenal of tools what with such low cost.

I'm sure your time is worth more than $49 dollars for Spreadlib and I think Zyrip's is Donation based while in beta. Pipe up if I'm wrong.

Regards Lagi

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: Possible to build a Excel-like Table

Post by Mikey » Thu Feb 09, 2017 4:57 pm

The trick is doing this without having to use Excel as the target to make it happen. I think only one of them does that, right? Zyrip's library does, I believe, and he was also talking about adding Google Sheets functionality into it, but I haven't had a chance to really have at it, yet.
http://www.aslugontheroad.com/download/ ... x-lib-test

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9802
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: Possible to build a Excel-like Table

Post by FourthWorld » Fri Feb 10, 2017 6:31 pm

Hans-Helmut wrote:Richard, you are a Livecode Expert and I really appreciate the forum and your continued contributions here. For a "beginner" - well, not a bloody beginner any longer, but still a beginner - your directions are helpful, and of course, any suggestion is just a big huge help.
You're very kind, but truth be told I'm still learning so much every day that I always feel like a beginner myself. :)
The logic here - and why I am thinking in the direction of Excel: My customers really love working with Excel, and much work is done using Excel even for writing invoices or doing any kind of calculations. The huge big drawback is that there is no work-flow and there is no real database. Also, it is too easy to enter data which is not checked and it happened more than once that people are entering wrong data, wrong calculations, copy and paste wrong rows or columns, etc.

So, the idea was to have a hybrid application - on one side it looks familiar to customers working with Excel - on the other side it provides the level of security and validity and data storage that only a tool such as LiveCode would provide.
Spreadsheets are seductive. They provide a reasonably useful general framework for laying out data and formulas to work on that data, and being so general-purpose they're quite popular.

But being such a jack-of-all-trades imposes two significant limitations:

They're expensive to build
For all the history of GUI computing, there have been only a few dozen feature-complete spreadsheet implementations. And most of them have died along the way under the weight of the expense of maintaining them. Today, despite the near-ubiquity of GUI computing, there are probably fewer than a dozen spreadsheets in common use, and more than 95% of the world uses only three: MS Office, LibreOffice, and Google Sheets.

The Big Three raise expectations that will be expensive to meet. The basics of a grid may be relatively easy, but to make truly meaningful layouts for the vast range of things people use spreadsheets for will require cells that can maintain their own background colors, optional borders on any or all sides, border colors, cell-specific font sizes and styling; images in cells; charts overlaid on cells; lookup and sorting of cell ranges, and much, much more.

A good spreadsheet implementation will take many programmer-years to develop, and some of those features (such as discrete cell attributes) would probably be best handled in LiveCode Builder rather than LiveCode Script, where you have more granular control over drawing instructions but that freedom will come at the cost of needing to conceptualize your work along algorithmic and structural lines more akin to working in lower-level languages like C.

They're often cumbersome to use
Given their ubiquity this may sound like heresy, but think about it: how often have you laid out a nice-looking spreadsheet only to realize what you really need is a new list included in between some other elements, but since the whole sheet is one fixed grid you can't just add that without altering everything above and below it? How often have you want to have a cell auto-wrap rather than truncate, while leaving others truncated? How often have you found that moving cells from one place to another breaks either their forumulas or those in other cells dependent on the previous locations? What does "Y242" communicate to the user making a complex layout?

People use spreadsheets as a catch-all for tasks when they have no other app better suited to handle them. We've come to accept them as a useful model because we're often using them for ad hoc tasks for which nothing else exists to handle the specifics of our workflow. And for quickie models that are relatively trivial they can be quite good. But for any complex layouts we often spend as much time dealing with their limitations as we do building our actual desired result.

Possible solution: Non-Spreadsheet
I'm assuming your app has a purpose more specific than being a general-purpose spreadsheet, yes?

If the goal were to build another spreadsheet, given the cost involved I'd just use LibreOffice and leave my development time for app categories where we don't already have excellent apps serving a saturated market.

So if my hunch is correct, that your interest here is to provide your users with a means of doing layout related to your app's specific purpose, and doesn't aim to be yet another general-purpose spreadsheet app, then you have a much simpler set of options that can both lower your costs and ease your user experience.

If we think about the usage patterns with spreadsheets, most cells boil down to three main uses: labels, input fields, or lists.

LiveCode provides all three right out of the box.

Of those roles, usually only labels and a subset of list columns will be filled in by formulas. And usually the input fields are the triggers for those formulas.

When you want the guidance of a grid to keep things aligned, you can turn on the grid global property and set the gridSize to anything you like, and things will snap into place easily.

How these raw materials can best be used to design a layout tool for your app will of course depend on your app's purpose and workflow.

My only aim here is to encourage thinking beyond the limitations of the spreadsheet model, to consider options which not only will be much more affordable to deliver, but if designed well can also be much simpler for your users to work with.

The more I work with LiveCode, the more frequently I find myself turning to it to build things I used to make in spreadsheets. I can throw down a few fields, write a few lines of script, and get a good result faster than I can figure out how to confine my thinking to a fixed grid. And the end result is inherently extensible; I can add anything I want anywhere at any time, without altering anything I've done before unless I want to.

In a sense (and pardon me if this comparison seems a bit abstract or just too steeped in ancient history) LiveCode is in many ways the fulfillment of the promise of Apple's old OpenDoc project, an infinitely flexible container of different object types that can be used to produce nearly any type of document needed. An input field here, a label there, a list next to them, with a button or even a slider now and then as neded - I can build just about any layout in minutes.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”