Need help with split items from list and merge them in another list

Maybe this csv can help you understand what i have in mind.
EXAMPLE-DATA.csv (562 Bytes)
This is the mainlist. Then i must separate fields so i can create a new list for feeding listpicker or listview with 3 or 4 fields (example: date, vehicle, item, price. And after selecting show in a label the mainlist data) or creating a new list with different order of fields (example: first column: date date for sorting descending order, reminder for sorting ascending order, date (month and year (i'll create hidden fields with that information at the next stages)) for calculating fuels and tolls per month etc
See these csvs:
EXAMPLE-DATA-per-date.csv (586 Bytes)
EXAMPLE-DATA-per-reminder.csv (586 Bytes)

Actually i find a way to do it, but i'm sure it's not the best way.
I created 6 lists all with join fields (all fields in one row). Per date short, Per date, Per reminder short, Per reminder, Fuel short, Fuel.
Per date short (in descending order) includes 4 fields: date, id, vehicle, "item"
Per date (in descending order) includes all fields: date, id, vehicle, "item", km/miles, price, reminder, notes
Per reminder short ((only if reminder field is not empty) in ascending order)) includes 4 fields with reminder first: reminder, id, vehicle, "item"
Per reminder ((only if reminder field is not empty) in ascending order)) includes all fields with reminder first: reminder, id, vehicle, "item", date, km/miles, price, notes
Fuel short (only if "item" field is equal to fuel (select from listpicker)) includes 5 fields: date, id, vehicle. "item"(fuel), price
Fuel (only if "item" field is equal to fuel (select from listpicker)) includes all fields: date, id, vehicle, "item" (fuel), km/miles, price, reminder, notes

Lists per date short, per reminder short and fuel short are for feeding 3 listviews in 3 tabs (results per date, results per reminder, results fuel).
When save button clicked i use add items to lists (6 lists with the above fields) and save them to TinyDb in the same time.
After selecting a record in listview (ex per date short), data from per date list appears in a label.
It's working, without messing records.
But i can't find a way to trick it so i can sum fuel prices for a period of time (month etc).
That is a reason (of many) that i need to split records (split text at comma) so i can use a single field (example: price for calculating cost for a period of time).
If id is not necessary, i can remove it.

what about using a SQL database? the local database is called SQLite...
using tables helps you to filter your data easily
for example

SELECT sum(price)
FROM myTable
WHERE date >= "2020-11-01" AND date <= "2020-11-30"

my sqlite extension is able to access the local sqlite database, there are also other sqlite extensions around...


Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by icon24 Taifun.

1 Like

Thank you for your answer, but it seems a little complicated for me.
In next stages i'm sure i will check it. For now i need to understand list behavior.

it will get complicated with lists...


1 Like

Here is a rough sketch of how I might approach this. I use two tables/lists, one for all vehicles, the other for all logs (maintenance items). After creating a vehicle, you can select its registration number (Reg No) from a list, then enter the log information. You can then recall all the log information for a vehicle by selecting its Reg No. I have used @Ken's Tableview extension to display the vehicle and log lists, this provides the option to select a row in the list and take action (for example to edit the entry) I haven't taken the example that far.

This is not a true database... if the number plate for a vehicle is changed (for example in the UK a personalised plate) then none of the log data will have the Reg No it is perhaps better to use the VIN.... or another unique and unchanging identifier.



CarMaintenance.aia (31.8 KB)

1 Like

Thank you very much. I will try it and I'll let you know.

Hi Johnl

It all depends on what the App has to do. In the case of your App, it is in fact much easier to use an SQL database as this is more flexible than Lists of Lists + TinyDb (which is a single, flat XML file). There is one other alternative, Google sheets, which work like a spreadsheet. TimAI2 is the expert on those, but it's an online solution. In my view, something like Taifun's sqlite extension is the best choice by far.

1 Like

Merry Christmas to all. I'm testing most of your suggestions. Soon I will show you my conclusion.

@TIMAI2 your solution works fine.
I followed your instructions and i managed to create tables for cars and logs.

I understand how to select items now.
Now i want a new "task". I want to sort logs by date.
I have a datepicker that supplies field textbox_date_1001 in format ddMMyyyy and a hidden field (textbox_date_for_sorting) in format yyyyMMdd.
I found an extension (list utils) (if posting link is against rules, please tell me to remove it) that sorts list by the first field (in my case textbox_date_for_sorting).
I don't know how to include a file with app (for other sorting solutions), so i selected list utils.
Now i want to create a new list from sorted list and select all items except textbox_date_for_sorting or hide textbox_date_for_sorting.
The reason is that here in Greece date format yyyyMMdd is looking weird.
I want to thank all of you for your help. @ABG method is very nice, @ChrisWard you helped me think to the right direction about databases, split records by car etc, @Taifun i will definitely look in the future about sql database. For now i prefer to stay with Tinydb. Thanks again.

Using the date format yyyyMMdd will allow you to sort by date (year, then month, then day).
This is a text (alphabet) sort.

If you

understand how to select items

, then you should be able to sort by the date format field ?

1 Like

I thought that i can do it. But i have problems, maybe from list utils extension.
Once i sort the list, i can't select from list and the tables show each result in one line with ().
My main problem is to select some of list item to create a new list.
As i said i want to sort by date (with format yyyy/MM/dd) and create a new list with the remain items from the sorted list, or a method to hide item from table.
You can see it in your project. I include list utils extension in it.
CarMaintenance_SORT.aia (39.0 KB)

use Do it to debug your blocks, see also tip 4 here
see also Live Development, Testing, and Debugging Tools

If you are asking for help, I recommend you to make it as easy for others to be able to help you ...
You probably will get more feedback then...

which means in your case post a screenshot of your relevant blocks including Do it results...

To download the aia file, upload it to App Inventor, open it, do some bug hunting for you, etc... this takes time, and most people will not do that...
Thank you.


Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by icon24 Taifun.

1 Like

WIth Tims' instructions, it works fine.

When i try to sort the list, i get error:

and the result is sorted but in one line without comma and without cells (cause the missing comma) and with () .

Now if i try to make a new list

I have this error:

With make a list in golballogs1:

I have this error:

Just a suggestion to make everything easier to manage, why not use dictionaries instead of the lists, you might find the error by doing so.

1 Like

A couple of issues:

  1. Your date sort format. It needs to be yyyy/MM/dd for every entry, these are strings (texts) not numbers. Therefore 2021/1/1 will sort differently to 2021/01/01. make sure all you sort dates are completely filled like this: 2021/02/25.


  1. Use Do it to understand what your data looks like at each stage. this will help you find the problem


1 Like

I wish a happy new year to all of you.
My issue is Solved. Issues caused by list utils extension.
I tried @abg sort method and with @TIMAI2 code i managed to select list items and create new list with the items i want.
Thank you very much all of you for your time and your efforts to help me .

One last thing that i want is to select record from tables view for editing or deleting. I didn't find a way so far.

Use this block ( I did include this in my example) to get the entire row or cell


1 Like

Thanks again :slightly_smiling_face:

You are sorting a three level deep structure.

You did not show your customized precedes() function.
Are you sure it compares the proper fields?

1 Like