151 Reply by brian.zaballa

(22 replies, posted in General)

blackpearl8534 wrote:

But i need filter on access db because i want to load filterd data not all...

Well, I'm not sure why you would want that when filtering in MVD with sqlite will give you less time to spend.
But if you insist, then try to integrate Filtering on MS SQL with script and queries.
I found some here
https://stackoverflow.com/questions/424 … access-sql

Then do something like

procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
var
    i: Integer;
    _id, _num, _txt, _date: String;
    _fdate1, _fdate2: String;
begin
    LoadStatus('Opening Database...');

    _fdate1 := FormatDateTime('yyyy,MM,dd', Form1.f_from.DateTime);
    _fdate2 := FormatDateTime('yyyy,MM,dd', Form1.f_to.DateTime);
    rs.Open('SELECT id,sampleNumber,sampleShortText,sampleDate FROM SampleTable WHERE sampleDate BETWEEN DateSerial('+_fdate1+') AND DateSerial('+_fdate2+')', Conn);
    //rs.Open('SELECT id,sampleNumber,sampleShortText,sampleDate FROM SampleTable WHERE sampleDate BETWEEN DateSerial(2021,08,04) AND DateSerial(2021,08,29)', Conn);

Go to forum: General

152 Reply by brian.zaballa

(22 replies, posted in General)

blackpearl8534 wrote:

great example. thanks..
can you tell me how to load data using datetimepicker filter

Just do a filter on your sqlite(temp) database

Go to forum: General

153 Reply by brian.zaballa

(22 replies, posted in General)

ehwagner wrote:

blackpearl8534,
Is there a reason why you can't use a temporary sqlite db for reporting purposes only.

I'm up for this. You can load it into a temporary table on your sqlite, then print it.
Here I made some modifications on the script to give you an idea.

Go to forum: General

154 Reply by brian.zaballa

(10 replies, posted in General)

Here, i got some changes

Go to forum: General

155 Reply by brian.zaballa

(10 replies, posted in General)

Well. it'll not save as you expected. If you want it to accept decimal, then change production_reports.quantity to Real.

That error occur because of comma. Just change it to dot (.). You can trap it by checking the property NumbersOnly

Go to forum: General

156 Reply by brian.zaballa

(10 replies, posted in General)

You can just add filter on your grid

Go to forum: General

157 Reply by brian.zaballa

(10 replies, posted in General)

That's just an example to give you an idea.

Here, I made some modification on your sample. Hope it can give you another idea on how you can do it.

You can just hide the negative value on the raw mat stock list, or have a field saying that it was a mats from a recipe.

I forgot to change the Grid title  (Total Received), it must be changed to Current Stock. If you want to show the total received, then you can just add additional calculated field on your raw_materials table

(SELECT total(quantity) FROM raw_mats_receive_reports WHERE raw_materials.id = raw_mats_receive_reports.id_raw_materials AND quantity > 0)

Go to forum: General

158 Reply by brian.zaballa

(10 replies, posted in General)

Check this one. This is from the post http://myvisualdatabase.com/forum/viewtopic.php?id=6995

Go to forum: General

159 Reply by brian.zaballa

(10 replies, posted in General)

The cascade is working just fine.

In that database structure, Owner is the Parent, and it has Child Vessel. The table having the id_[ParentTable] field is the Child. That's how One-to-Many table works.

Setting id_[ParentTable] to cascade will allow or automatically delete the record connected to the Parent. That's why when you delete Owner, it will automatically delete its child Vessel (only child since you only have one vessel for each Owner). That's how the cascade works.

In that case, in my sample. Owner is the Parent, it is how database work, but since in your business logic, I just make a little tweak when deleting the Vessel (script in Vessel Delete button). Tweak to automatically delete Owner when it has been deleted since there's no such function in SQL to automatically delete Parent's record when deleting record from its Children (not really tho. you can also create a trigger for auto-delete but that'll make it more complicated unless you are familiar with it).

Problem with this approach is when you connect other Child table of the Owner, say for example Owner having Transaction table, or Sales or whatsoever that is connected to the said table(Owner), automatic deleting this(Owner) will throw you an error if you didn't select cascade for the other child. That's another topic by the way. You must know all the possible connection of the Owner before applying this approach.

If you are not doing anything with Owner table(this is my assumption since you are automatically deleting it after deleting a Vessel), then why not just include it in Vessel's table as fields? I mean all its fields(or just the important ones).

Go to forum: General

160 Reply by brian.zaballa

(9 replies, posted in General)

Maybe something like this?

Go to forum: General

161 Reply by brian.zaballa

(10 replies, posted in General)

Good day Frank,

From your statement, 2 - most Owners have only 1 Vessel, Will there be Owner/s that eventually have more than one vessel at a time?

The update queries I made there is just to fill up the gaps on the logic. as you can see, used(is_used=1) owners will not appear on the vessel form when you create one. This will prevent having an Owner with multi-Vessel (for this will cause of the foreign key error when auto-deleting the Owner). I just go with the business logic you are trying to do. But again, in my opinion, there's no need to automatically delete owner after deleting a vessel.

For the child-parent, from my sample(copied from your pdf), Parent table is the Owner, and it has Child Vessel following the One-to-many relationship.
One Owner can have one or more Vessel/s.

Again in my opinion, to save you from that trouble of having that additional queries, scripts and checking if the owner is already in a vessel, don't auto-delete Owners that will no longer have vessel or after deleting a vessel. Easiest way to do that is to have another module on your application that will check all owners that have no vessel, then delete them,

Go to forum: General

162 Reply by brian.zaballa

(10 replies, posted in General)

Oh, I think I got what you are trying to do. Why would you want to delete Owner's record after deleting Vessel?
Based from your database structure, Owner can get one or more Vessel. So if you put a cascade on Vessel.id_Owner, then deleting a record from Owner will also delete all Vessels under the owner. That's how it works with the structure. That's how child-parent works.

I'm just confused on why, but if you really want Vessel to be the parent, then Owner must be the one connecting to the Vessel and must have the id_Vessel. But it's like saying that a Vessel can be owned by one or more Owner.

If you really want that approach, here I made a sample to give you an idea.

Go to forum: General

163 Reply by brian.zaballa

(10 replies, posted in General)

I'm not so sure on the problem too. Maybe you can upload a sample application with the tables associated with the problem and upload it here for us to see the problem. Cascade option should be fine in this case.

Go to forum: General

164 Reply by brian.zaballa

(10 replies, posted in General)

papafrankc wrote:

Hi All,
I'm having some problems with setting joins between tables. I believe it should be pretty basic and straight forward.  But I'm getting myself confused (easy to do sometimes lol).
Here's what I would like to do. Please refer to the attached PDF file.
.
- Vessel should be the PARENT table
- Owner should be the CHILD table
- If I DELETE a record from the PARENT table, I would like to also delete the corresponding record from the CHILD table (Owner)
- Simple right?
- So logic tells me to join the Owner table (child) to the Vessel table (parent)
- However when I do that, it screws up all kinds of things in my application.
.
The way I have it in my attached pdf file seems to work the best for my application, however when I delete a VESSEL record it DOES NOT delete the OWNER record.
- I understand why my solution is not working but I can't seem to come up with how to make it work.  I think, as I said that I'm confusing myself.
.
Right now I can delete a record from one of the other tables - MANUFACTURER, SUPPLIER...etc.  However I am NOT able to delete an OWNER record without first deleting the VESSEL record.  I don't understand this one because I don't believe I have the Cascade option set in any of the joins??
.
Note: in this application I will have - one Vessel and one Owner.  The other tables, Manufacturers, Suppliers, Equipment...etc. will have many entries.  I would like to leave the data in these tables even if the user deletes a Vessel and Owner and replaces them with a new Vessel and Owner.
.
Some thoughts about how PARENT/CHILD joins should be properly set up would be appreciated.
.
Thanks
Frank

Did you try checking the Cascade option?

Go to forum: General

165 Reply by brian.zaballa

(7 replies, posted in General)

k245 wrote:
brian.zaballa wrote:
sibprogsistem wrote:
frmStart.SQLConnection.Connected := False;

Thank you. This one helps.

Should this command be placed in the "begin end." section? According to my observations, connecting to SQLite occurs immediately after creating all the forms before opening the main form.

Yes. Thank you for pointing it out. Tried it again and I think UpdateDatabase('') still runs in background even placing the code in the begin end. section

Go to forum: General

166 Reply by brian.zaballa

(7 replies, posted in General)

sibprogsistem wrote:
frmStart.SQLConnection.Connected := False;

Thank you. This one helps.

Go to forum: General

167 Reply by brian.zaballa

(7 replies, posted in General)

up

Go to forum: General

168 Reply by brian.zaballa

(25 replies, posted in General)

domebil wrote:

in fact, it either makes it open source or sells the Drive Software company.

Agree. Make it an open-source if the company cannot maintain it. There's a lot of potential on the MVD software. It really helps creating database application in an easy way. There's a lot of masters here to help it grow. smile

Go to forum: General

169 Reply by brian.zaballa

(1 replies, posted in Script)

michaels429 wrote:

Hi,
Is it possible to create my own class, instantiate that class and keep track of all objects?  If so, does anyone have a sample MVD project?
Thanks,
Mike

Can't do that, you can only create your own procedure and functions unfortunately.

Go to forum: Script

170 Reply by brian.zaballa

(7 replies, posted in General)

try to minimize using a subquery by saving it as a field and not as a calculated field.
a. If you are familiar with trigger, you can use it to save the data after adding or updating a record.
b. if you only need it(the calculated) on a report, then having a query that will save the data in a filed before the report will be nice too
c. adding a hidden field on the form that will get/save the calculated as a field on database before saving the record.

note: you need to perform a one-time query to update the existing records in the database.

Go to forum: General

171 Reply by brian.zaballa

(7 replies, posted in General)

Check on your calculated fields. having bunch of calculated fields with a subquery might slow down your application with bunch of records.

Go to forum: General

172 Reply by brian.zaballa

(1 replies, posted in General)

I've made a temporary fix on this matter by converting a code snippet from PHP.

Feel free to use and modify it if you need this function in your application.

I hope a fix will be available soon though. I think it'll be only a 2-letter fix in the ToWords function. smile

Go to forum: General

173 Topic by brian.zaballa

(1 replies, posted in General)

Good Day MVD Dev.

I've been using the function ToWords for our applications. Unfortunately there's a bug. Attached is a sample program showing the bug. I hope you can include a fix for this. We already deployed our applications and we have to make an update to fix the printing of currency.

Thank you in advance.

Go to forum: General

174 Reply by brian.zaballa

(30 replies, posted in General)

Grete wrote:

wow perfect - thank you very much :-)

Other way is to have image as background.

Go to forum: General

175 Reply by brian.zaballa

(9 replies, posted in Script)

wenchester21 wrote:

Regular expression for dates

^(((0[1-9]|1[012])/(0[1-9]|1\d|2[0-8])|(0[13456789]|1[012])/(29|30)|(0[13578]|1[02])/31)/[2-9]\d{3}|02/29/(([2-9]\d)(0[48]|[2468][048]|[13579][26])|(([2468][048]|[3579][26])00)))$

Woa, that's a complex one. I really not into regex. LMAO. Anyway I think it is because of the leap year, and other things to check on date. I hope MVD Dev will update or create a sample for that TRegexp. I just don't know if that class TRegexp is derived from TRegexpr which I find workable. I'm just a newbie when it comes to pascal delphi. smile

Go to forum: Script