NSBase / Forum / General Discussion: Sample for Asset Tracking Database?

Freen - 2020-12-18

Hi, would or is someone working on an Asset Tracking database and able to assist with a sample?

For the moment, I'm looking at something similar/simple to the MS Access Database sample as shown here: https://support.microsoft.com/en-us/office/video-use-the-desktop-asset-tracking-database-template-720f1ed2-7495-435e-934c-7216df268551

Assets, Asset Owner with details / Asset Age (date function, due to be serviced/tested) and physical location of the asset.

I don't mind contributing for time spent and/or work in collaboration to get something together. I got a good understanding of the works with NSBase as a great alternative to MS Access, but still lacking that extra bit programming knowledge and struggle to get a decent, functioning database together.

@surfvite, I have tried sending a PM to you, and my apologies if this is not something you like to see posted here. I hope it is ok to ask for help like this?

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

neuts-jl - 2020-12-27

Hello
I have been very busy with other activities
I will look at your need and answer later.
ps) I did not see your message in MP

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- Freen - 2020-12-28
  
  Hi surfvite, I hope you've had a nice Christmas and thank you for looking into it, much appreciated.
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

neuts-jl - 2020-12-30

Hello ,
I have built an app for you from a MsAccess sample app
here is the link
https://sourceforge.net/projects/nsbase/files/samples%20sources/assets.nsb/download
Have fun

👍
1

Last edit: neuts-jl 2020-12-30

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- Freen - 2020-12-30
  
  Hi surfvite, wow, I can't believe how quickly you got this together, I have been trying and struggeling to get something similar done. This is a great start.
  
  Thank you so much!
  
  I hope the contribution and my message will reach you.
  
  Have a happy new year for 2021. I hope it will be a better one than 2020 has been.
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

neuts-jl - 2020-12-30

Thank you
I made this application during the low times, a short day

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

neuts-jl - 2020-12-30

Thank you Freen for your participation in the Leetchi fundraiser, you are the first, and your participation is very important.
I hope other people do the same. It is not to enrich myself, but to buy a MacBook in order to support the development of NSBase on Mac

https://www.leetchi.com/c/macbook-for-nsbase

Merci Freen pour votre participation à la cagnotte Leetchi, vous êtes le premier, et votre participation est très importante.
J'espére que d'autres personnes font de même. Ce n'est pas pour m'enrichir, Mais pour acheter un MacBook dans le but de pourvoir porter le developpement de NSBase sur Mac

https://www.leetchi.com/c/macbook-for-nsbase

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Freen - 2021-01-02

Hi surfvite, I'm working on the assets sample. I have added another table for Accessories and try to integrate them too. I'm struggling to add the DBGrid connections to the correct data/table.

Possibly a bug? Form Assets, when clicking on on of the dropdown fields and not changing/selecting anything, the record is no longer displayed (it doesn't seem to change the record).

Form Assets, added new tab 'Accessories' - I want to display all accessories belonging to the selected asset record (not working as intended at the moment, it displays assets, not accessories)

Form Assets, tab 'Accessories', button 'Tree' - I want to display only the assets with their accessories and able to move them from one accessory to another this way.

Form Accessories, I want to display all Accessories if there is no 'Asset ID' (or '0') selected for the active record in the DBGrid part and any other accessories already associated with that asset.

And then be able to select an Asset for the asset ID (how can I have a "default" there, like '0', or no associated asset to begin with, to avoid errors?)

Form Accessories, when clicking 'Edit' twice, it reverts to the non-editable state and can't be changed again. i.e. clicking on 'Edit' again does not revert back to edit mode. I guess I've done something wrong with the boolean states?

My next challenge: I'm working on another 'Maintenance' Form, displaying assets by Maintenance date, where I'd like to display all Assets sorted by their Maintenance due (or overdue) date and highlight the dates. Due/Overdue dates in red and dates due in one month or less in yellow.

assets(7).nsb
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- neuts-jl - 2021-01-02
  
  Your Dbgrid dbgAccessories does not have a specified data source
  a parameterized request must be created which will be the dbgrid datasource
  the parameter will be the ID of the asset cer it is necessary to control this grid on the accessories of the asset (see hidden objects)
  there is no action script on the onchange of the dbgrid accessories, to initialize the AssetID field of the accessory
  For the accessories tree, ditto no datasouce, no query created ....
  I will not have made a tree specific to the accessories, I will have added them to the main tree at the first node
  On the other hand you will not be able to move accessories
  in this tree, because the data structure does not allow it, it is necessary to provide a form, or to integrate this functionality directly in the form Assets
  I watch this tomorrow
  
  Then I don't understand what you want
  
  Last edit: neuts-jl 2021-01-02
  
  assets next.nsb
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Freen - 2021-01-03

Hi surfvite, thank you for the quick check and fix/solutions!

Accessories tree, in order to move them, data structure needs to be like your sample instruments.nsb?

I didn't see the hidden queries (>_<)

How to display currency format in the textboxes and in Gridview? ex 2000 -> € 2,000.00

Updated sample:

Form Employees, the original layout was very interesting, automated display of data fields in pnGridLayout - nice solution (sorry, I manually changed it now).

Form Employees, how to display only 'active' Assets, ignoring historical entries? And showing all entries when selecting the tickbox?

Form Accessories, Asset ID field, I can't seem to display the existing data, when opening the record? How to display only the description, without the number?

Form Accessories, how to display the Accessories already associated with the selected asset in the Asset ID field?

Form Accessories, when clicking the 'Edit' button a second time, the form is locking again, how can this toggle or subsequent clicks be ignored? I've tried different Boolean script, but I can't seem to get this to work better.

Last edit: Freen 2021-01-03

assets_next2.nsb
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- neuts-jl - 2021-01-04
  
  Some answers :
  1: yes
  2: No problem
  3: I must first declare the fields in cash, I look for the text fields ...
  I have trouble understanding the other questions, I don't see what you are doing, it's pretty confusing and all over the place. Language problem, my natural language is French
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
  - Freen - 2021-01-05
    
    Sorry about that, I'm been trying to do too many things at once, instead of solving one issue at the time. My natural language is German. ;-)
    
    I was looking into translation of the controls too as I don't speak French at all (other than Google translate), I found your solution for your sample contacts.nsb particular neat. :-) - When looking at that for the first time, I could not work out how the language controls work, except for the 'navigator' control, until I've found the language (text) part under the project languages.
    
    I will try to "stay on track" and elaborate on the forms one-by-one.
    
    If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
  - Freen - 2021-01-05
    
    Form Employees:
    
    I want to display only active assets (status 'In Service') when opening the form Employees. Only when ticking the tickbox 'Include History' I want to see all assets (status 'In Service', 'Inactive' and 'Sold') for this employee.
    
    If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
  - neuts-jl - 2021-01-07
    
    I redid the currency symbol hunt in NSBase
    In the next version the formatting of data, particularly monetary data, is more fully understood. This does not prevent the data must be in the right type when they are created in the tables.
    
    If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Freen - 2021-01-03

I've added form 'Maintenance Schedule' - I've managed to get them more or less displayed as I wanted ("missing data" is always a problem :o)). But would like to have highlight formatting on the date column, date due (today), or overdue, older than today in red and today's date less than 1 month in yellow. I also don't know how to be able to open the form 'Assets', when double clicking a record in the displayed DBGrid.

assets_next3.nsb

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- neuts-jl - 2021-01-04
  
  There are some posts in the forum regarding conditional formatting of DBGrids notably with dates and in the examples there is quite a lot of double click use on the grids.
  We must add in the view, a column containing the date difference of the next maintenance with today and take it into account in the conditional formatting, I have highlighted the complete lines, but we can do it field by field ....
  
  assets_next3b.nsb
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
  - Freen - 2021-01-05
    
    Very nice, thank you very much! - I don't know why, following the conditional formatting from previous discussions, I was not able to get this to work at all. I'm not sure what I did wrong initially, as your formula looks not much different from what I did before.
    
    But there seems to be a bug in the function?
    
    JulianDays ('now') -1, -2 and -3 do not toggle clYellow
    
    I couldn't get it to work at first in my sample, until I've changed the dates...
    
    In form Maintenance Schedule (frmMaintenanceSchedule), The Script shows:
    
    function frmMaintenanceSchedule:DBGrid1ActionOnDblClick() nsbase.window.show('frmAssets') local ds=nsbase.datasetCreate('frmMaintenanceSchedule') local id=ds.getFieldname('Asset') ds=nsbase.datasetCreate('frmAssets') ds.locate('id',id,'first') end
    
    Something peculiar (may cause a bug in the future?): You've renamed the DBGrid from DBGrid1 to dbgMaintenance after the function was saved - the function does not update the name in the script, if the name of the application is changed?
    But I don't think that seems to matter for the function of the Script?
    
    Last edit: Freen 2021-01-06
    
    If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
    - neuts-jl - 2021-01-07
      
      Hello
      The data must always be valid and of the correct type.
      You have to know how to tame the SQLite and LUA coding which are used by NSBase.
      The renaming of objects does not influence the script, automation has its limits, and there must always be human vigilance ...
      
      If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Freen - 2021-01-04

Not sure what could be wrong, but when trying to edit data in table Accessories, I'm getting an error message "error: no such table: main.nsbase_tmp"

tmp_table_error.JPG

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- neuts-jl - 2021-01-04
  
  It does not matter. This is an intermittent bug, which I haven't managed to track down yet. I know it's on the linked tables.
  To fix this while waiting for a fix: just make a bogus modification in the table in question and save it, it should work again
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
  - Freen - 2021-01-04
    
    I've tried several times, the error seems to be permanent with that table :-(
    
    I believe this error started, after I had some trouble creating/setting up a filter (view), where I may spotted a bug?
    I usually set up a view, using the table tools, ticking the columns to use in the query and linking the tables. When setting up a new query, I used the table tools, then changed to sql view (ticked the box for sql), edited the sql (copied code from another query) and removed the sql tick, to see if or how the code shows as column view. It reverted back to the initial sql code and the column view without the changes I've done. Placing the sql view tick again, showed the code I've done. Basically there were 2 sql codes in one view. I hope that makes sense?
    
    If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Freen - 2021-01-05

This is my latest update. Thanks to your script code for the dbgrid, I've updated the button functions in form Assets (frmAssets), so they open the others forms and display the data from the record shown in form Assets. :-)

assets_next3c.nsb

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Freen - 2021-01-10

Hi surfvite, thank you for the latest update. I've tried out the new export functions:

Using sample database assets.nsb, I can't see any of the reports in the list of items that can be exported? I assume this is correct, only (resulting) data is exported, not script of a view?

Properties for TextEdit or MaskEdit components, DisplayFormat, what are the options or what expression can be used in that field?

Form Maintenance Schedule - if today is 10/01/2021: 10/01/2021 = clRed, ok, but dates 11, 12, 13, 14/01/2021 do not show clYellow - is that a bug?

Form Employees - I want to display only active assets (status 'In Service') when opening the form Employees. When ticking the tickbox 'Include History' I want to see all assets (status 'In Service', 'Inactive' and 'Sold') for this employee.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- neuts-jl - 2021-01-10
  
  Hello,
  1) I don't quite understand your question,
  The import / export function in the home / import tab has not changed
  I have added an import / eport function in the form / report editor which thus allows easy exchange between nsbase applications, forms or reports.
  
  2) DisplayFormat property is used in conjunction with DisplayCurrency or DisplayNumber. If the displayformat property is empty, it will be those of the project that will be used, see the 2 formats for decimal and monetary digits in the Project / Settings / Active project: generalities tab.
  
  3) Yes indeed it does not work, it seems to be a bug, I check and correct if necessary
  
  4) Several errors
  You must also declare the status parameter in the request
  Checked referral 1 or 0
  cbActive is useless
  Redundant code and a bit of reworking of this code
  
  You will find the correction in the attachment
  
  Last edit: neuts-jl 2021-01-10
  
  assets_next3c (1).nsb
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
  - neuts-jl - 2021-01-10
    
    2)The data from the request are not numeric typed, and the DBGrird tests the type to do its tests
    I modified to take into account the type of the final data, and will be available in the next version
    
    👍
    1
    
    If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Sample for Asset Tracking Database?

NSBase is a database management system.

Forums

Help

Sample for Asset Tracking Database?

Sample for Asset Tracking Database?

NSBase is a database management system.

Forums

Help

Sample for Asset Tracking Database? document.SUBSCRIPTION_OPTIONS = { "thing": "topic", "subscribed": false, "url": "subscribe", "icon": { "css": "fa fa-envelope-o" } };

Sample for Asset Tracking Database?