301 Reply by CDB

(8 replies, posted in General)

Sorry about that Derek, I thought I'd included everything.

File now attached.

Sigh, must remember to click 'add file'.

Go to forum: General

302 Reply by CDB

(8 replies, posted in General)

Hi Derek,

I have almost solved the problem apart from duplicate entries. I think i need a 'group by' or a 'where' clause to stop that problem. Or of course I've just got the whole thing wrong!


The way I intend this to work is the Tech will look up the internal part number (LineNumber/description ) this will link to all the suppliers for that part. Then they will choose which supplier (if that part has multiple suppliers and the correct supplier part code) and the quantity they require.


The person that actually places the order will fill out the PO/Req number (Reference).


When the parts arrive the Tech in charge of part distribution will look up the supplier; enter qty received, and then distribute it to the tech who requested the part.


The grid i have in place is for an overall view and eventually the selection boxes and text edit are for searching.


There will be extra forms for data entry and part information. A third tab or form will list by searching for a Parent part and listed below will be all the child parts that can be used with their Line number and supplier part number and picture.


That is the intention of the complete project.  For the moment I just need to get the overall view grid working, and the rest I hope will fall into place.


I should add in case you are wondering - the line number 9999999 is for those parts that have not been allocated a company line number of which there are many!

Go to forum: General

303 Topic by CDB

(8 replies, posted in General)

I am having a problem (i think) with the way MVD allocates the primary key ID. Is there any way from within MVD to have a 'composite ' key?


My problem:


I need to allow for a product to have multiple suppliers.


I would like to display in a tablegrid:


The person who ordered a part: The supplier Name : Order date: Product: Qty ordered. (This is just the basic  list of fields).
The current set up fails when I try to include the supplier name (or anything from that table). The error is that that column does not exist.  I get the same error if I directly type the SQL into DB Browser for SQLite.


Currently I have an intermediate table that contains the product_Id from the products table and the supplier_Id from the suppliers table, both set as a foreign key.


I think my problem might be that I need to actually have the supplier_id in the intermediate table as the primary key or some form of composite key.


The SQL that MVD produces is:

SELECT DISTINCT "part_suppliers"."supplier_name", "orders"."date", "orders"."order_ref","products"."part_num","technicians"."f_name","orders"."id","orders"."id_technician"

FROM "orders" LEFT OUTER JOIN "technicians" ON "orders"."id_technicians" = "technicians".id

I'm wondering if to accomplish what I'm hoping for, I need to manually create the tables and SQL scripts?

The schema I'm using is here http://myvisualdatabase.com/forum/viewtopic.php?id=6190

Go to forum: General

304 Reply by CDB

(3 replies, posted in Script)

I think you need to use a calculated field. One thing to remember is you need to add '40' to any minutes over 60 to get the correct time.

Example 1:30 + 3:45 = 4:75 (decimal) you need to change it from base 10 to base 60 so the sum should be -


1:30 + 3:45 + 40 = 5:15 - in other words add the minutes up separately and then add any carry to the hour calculations.

Actually I'm rethinking my first comment, it might be better to perform that in a function - but others here might have a different view.

Go to forum: Script

305 Reply by CDB

(6 replies, posted in General)

Thanks for the idea Derek,  but that makes no difference.  It is not a great problem, I can live with it.

I might try it on a different PC and see if it is indeed a quirk of the PC I'm developing it on.

Go to forum: General

306 Reply by CDB

(6 replies, posted in General)

Hi Derek,


Project uploaded! Note I have made the alteration suggested by Jean in the project.


I might make just a plain form in Delphi or Lazarus and see if I get the same result.


Just as an aside do you know if the graphics.dll which has appeared in my files actually contains the png files I am using on this project?  The total of the size of the individual files are very close to the size of the DLL file.


Thank you for having a look.


PS: I am aware of a foreign key problem in the DB causing duplication when supplier names is used.

Go to forum: General

307 Reply by CDB

(6 replies, posted in General)

Thanks for the idea Jean, but sadly that makes no difference.


And if you started by setting the position property of your form on poScreenCenter from the Object Inspector.?

Go to forum: General

308 Topic by CDB

(6 replies, posted in General)

I have a form which has constraints as to maximum height and width when the maximise button is clicked.

I have the following code to make sure the form stays centred on the screen when maximised.

procedure Form1_OnResize (Sender: TObject);
begin
  form1.Left := (screen.Width - form1.width) div 2;
  form1.Top := (screen.Height - form1.Height) div 2;

end;

The problem with this is that the form visibly jumps between the top left corner and the final resting place as per the code above.  Does anyone have any ideas to workaround this and make the screen centering more smooth?

My default form settings are:


Position = poMainFormCentre


Width = 952
Height = 461


MaxHeight = 750
MaxWidth = 1500


Min height and width is zero

Go to forum: General

309 Reply by CDB

(11 replies, posted in SQL queries)

I realise this is an old post, but, the solution provided uses a database table to store the pagination increments.

If you don't want to have an extra table, you can just add items to the combobox as a string.

So the code could be changed to:


    Form1.comboBox1.ItemIndex:=4;////this will show the first 100 records
    Form1.GridEmployees.dbLimit := StrToInt((Form1.combobox1.Text));
    Form1.bSearch.Click;
    form1.ComboBox1.Items.Clear;
    form1.ComboBox1.Items.Add('10');
    form1.ComboBox1.Items.Add('20');
     form1.ComboBox1.Items.Add('100');
    form1.combobox1.Items.Add('500')

I found this topic while searching to see if there was a way to paginate a gridtable by clickable page numbers at the bottom or top of the table, but it doesn't seem possible with MVD

Go to forum: SQL queries

310 Reply by CDB

(3 replies, posted in General)

Hi Derek,

Thanks for looking at the schema.

1. The manufacturer is the company that supplies the parent part, the supplier is a company that supplies replacement parts as a 3rd party - so for example - you could have a car and the manufacturer is Austin (yup it dates me), now replacement parts could be purchased from Austin or  from a variety of 3rd party after market suppliers. Although mine is to to with electronics.


2. I thought that they would be tied via the foreign keys combined between the Products and Order_Products tables, but perhaps I should  the supplier and manufacturer FK into the actual orders table.


4. I had considered having a status column, hadn't thought of a separate table, I'll have to think about that. I was thinking along the lines of having back order entries show up as a different colour, which  I suppose could be easier to do.

One of the reasons that I separated out suppliers and manufacturers is that one of the queries/view will have nothing to do with ordering. It will be a reference so the parent part number is entered and all the available substitute parts can be listed.

A real example:

Parent part is an LCD monitor - I look up the internal part number and this will now display a list of all the spare parts that will go into that particular monitor, their internal part numbers and the choice of suppliers.

12345 LCD Monitor   - parent part   - Manufacturer  some - brand - name
  304123 Backlight inverter_1  -  subordinate part - from supplier A  supplier part no: 123-9876
  304124 Backlight inverter_2         "   from supplier A or even supplier B     part no: 123-4567  and INV26
  304155 scalar Card 

etc.     

Originally I was going to make two programs, but that would  mean entering part numbers etc into two different programs, when I hope I can just have one program and just have different forms to display orders and product reference.

Go to forum: General

311 Topic by CDB

(3 replies, posted in General)

Hello again,

I'm about to start on my BIG order system project, and before I go too much further I thought I'd just sound out other peoples thoughts.


I have the problem that an item let's call it 'MyWidget' can be purchased from more than one supplier and therefore each supplier will have a different order code.


I think I've solved this by having a table called 'product_supplier' which consists of a foreign key supplierID from the suppliers table and a similar ID from the products table and the other entry will be the 'supplier part number'.


Now when a new product is sourced, the products table is filled with the product details and FK_ID's to the part_suppliers_table and the supplier part number to the product_supplier table. 


Does this sound the correct way to account for MyWidget being available from multiple suppliers?


My other question is, I need to be able to account for 'backorders or To Follows', which is going to mean only a part quantity of an item has been delivered.  Would it be best to create a 'backorder' table or just use a calculated field to work out quantity received  - quantity ordered?


Any thoughts?


I've attached the database schema for reference.

Go to forum: General

312 Reply by CDB

(3 replies, posted in General)

Hello,

ehwagner and dbk, thank you for your replies.

I don't know what was wrong last night, this morning I tried again multiple ways and each time the tables were created correctly.

Unless a reboot after install sorted the problem out it is just going to have to go down to one of those things where you doubt your own sanity.

Go to forum: General

313 Topic by CDB

(3 replies, posted in General)

I have downloaded the latest version of MVD and have hit a problem.

1. The database file is created, but no tables are.

2. (not a problem, just an observation) - just by creating a new project an 18.7MB EXE file is created and it is dated as modified MONDAY 11:19  even though I just created 10 minutes ago at 22:12 Wed 18/03/2020.

I've tried saving the file, running using the green arrow, it just does not create the tables in the sqlite file.

Anyone else able to replicate this problem?

I did install the new version straight over the top of the old one, should I have performed an uninstall first?

Go to forum: General

314 Reply by CDB

(2 replies, posted in General)

When you click on the green arrow an EXE file is automatically created. 

You also need  to  provide the script.dcu (not sure if you haven't created a script that is necessary), and all the files apart from the VDB file.

If you have created reports they need to be provided as well.

Go to forum: General

315 Reply by CDB

(5 replies, posted in Reports)

Well thank you Derek,

I thought I had tried another group footer and couldn't get it to work. Anyhow, your solution has solved my problem.

Go to forum: Reports

316 Reply by CDB

(5 replies, posted in Reports)

After some nutting out and looking at JoshuA's Derek solution, I have revamped everything and now have everything where I want it (almost).

I can see no way to have the Person name show at the bottom of each list, so have changed that to the top of each list.

I'd like to know if it is possible to have my demarcation dotted line only appear at the bottom of each supplier instead of after each Person name group.  I have had a fiddle with footers and child bands, to no joy.

I can live with the result I'm getting if i have to.

Now all that remains is to restrict the printing to a date range, rather than the whole DB each time I print.

I've attached my latest effort.

Go to forum: Reports

317 Reply by CDB

(5 replies, posted in Reports)

I've just seen JoshuA's post,

This looks very similar to what I'm trying to achieve - I'll investigate his and Derek's example

Go to forum: Reports

318 Reply by CDB

(5 replies, posted in Reports)

The design page layout attached

Go to forum: Reports

319 Topic by CDB

(5 replies, posted in Reports)

A plea for some help again please.


I am trying to persuade Fast Reports to output the following.


In a group:


Supplier Name
Information about orders pertaining to that supplier
Person the order is for.
(repeat all orders and tech names for that supplier)
(move on to next supplier)


I have had some success, I can get the Supplier and information to display, I'm having a problem displaying the person the order is for.


I did manage to show just one person, but only for the first entry. I've now managed to lose even that capability.

So, my current problem is:

1. getting the person name to print or show.

2. Prevent printing of Suppliers who have no current orders .


I have used  Dmitry's SubReports example as reference and have read the Fast Reports user guide for FR 4.0 but don't seem to be able to achieve the results that I'm looking for.

So just to recap, I'm looking for this result.

Supplier 1
item A_1
item B_1
item C_1
person name 1


item A_2
item B_2
person name 2


etc


Supplier 2


repeat as necessary


Supplier who has no orders - should not appear in the list. In the test case this would be Harbournes Haulage and Gametek.


I've attached a screen shot of what I'm currently achieving which is almost correct.


The code I'm using in FR4.0 is:


procedure MasterData2OnBeforePrint(Sender: TfrxComponent);
begin
    MasterData2.Visible := <Technician."id">=<Requests."id_techname">;
    // MasterData2.Visible := <Requests."id_techname">=<Technician."id">;        
end;


procedure MasterData3OnBeforePrint(Sender: TfrxComponent);
begin
    MasterData3.Visible := <Supplier."id">=<Requests."id_Supplier">;  
end;

Go to forum: Reports

320 Reply by CDB

(4 replies, posted in General)

What information are you needing to store?

Is GAMMU the software interfacing to the phone and the database then stores what GAMMU retrieves?

Go to forum: General

321 Reply by CDB

(9 replies, posted in Script)

Derek and ehwagner,

Thank you both for your help.


1. I hadn't realised  I didn't have to accept the foreign key offered by MVD and could substitute my own entry from a database table.


2. I rather like tabs instead of forms popping up, but Derek's solution is much simpler and less hassle. So, that will save me from writing some code to auto-adjust the column widths of the table grid if the user resizes the form.


This project is in fact the precursor to a far more enhanced version.


Sorry for the extra work I have put you and other others too, but I'm the sort of person that has to understand how a program works, plus I'm more used to from a coding point of view  writing in ASM or Pascal/C for embedded microcontrollers and I tend to make all my own code functions, hence procedural coding rather than objective coding.


I shall take Derek's suggestion, make a couple of slight alterations and then add in a report form so I can email  all parts ordered grouped by supplier, though I suppose I could do that using the print function and print to PDF.


I'll have to ponder on that.

Go to forum: Script

322 Reply by CDB

(9 replies, posted in Script)

I see how my table options are different to the demo program you kindly provided.


I have 3 tables currently and  the table that links to the supplier table has a foreign key in it and is marked as a dictionary. When I  open the 'ForeignKey' property I only get the option of the linked ID, unlike your program that access the 'name' field directly.


I think this might be why I can't get my design to work the same as your design.

Go to forum: Script

323 Reply by CDB

(9 replies, posted in Script)

ehwagner,


I'll give it another try, and see if I can see why it didn't work for me the first time around. Less code is always better! smile

Go to forum: Script

324 Reply by CDB

(5 replies, posted in Script)

@thezimguy,

Thank you, I like your idea of turning two of the edit boxes into comboboxes, and  your 3 lines of code is neat and succinct.


Two questions though:


1. What does the extra foreign key 'techID1'  actually do?  I've looked at the constraints for that key versus the original techID, but can't see the advantage.


2. I've never understood what the property 'dbGeneralTableId'  actually does.


@Jean.brezhonek,

I was aware, and have been trying to decide which option to take. I think the hidden button way is probably the way to go.   Sadly when a search is performed, I lose the double click action on the grid - in other words it doesn't do anything. But i can live with that until I work out a workaround.

Go to forum: Script

325 Reply by CDB

(5 replies, posted in Script)

I've just realised that the problem lies in the strToInt function.

I think that the easiest way out of my dilemma is to change the database to expect a 'TEXT' entry, and then use the Numbers only property set to True, and an entry mask of 7 digits.

That should solve the problem, no?

Go to forum: Script