Multiple Entries Form With Common Fields

Creating and using forms
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: [Solved] Multiple Entries Form With Common Fields

Post by Maax555 »

Think i have found the problem. The autorefresh associated with the Select Custom Form was showing "Sales_Purch_Form" only for the value. I updated this inline with your example and changed it to "Sales_Purch_Form/ListboxSalesRef;Sales_Purch_Form/ListboxCustPurch".
However the same issue is there that the CustomerID is not being saved into the record.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Multiple Entries Form With Common Fields

Post by Villeroy »

Well, there is already a customer ID associated to the sale or purchase respectively. I don't know why you store another customer ID in the table of part numbers, so I left this out for now. I'm not sure if your database is usable anyway.

How to pass the customer ID from the master form through the intermediate form forward to the data form:
Open the forms navigator.
Source of intermediate form:

Code: Select all

SELECT "Filter".*, :pCID AS "CID" FROM "Filter" WHERE "FID"=2
Link master field: INT1 (from the master record where FID=1)
Link slave field: pCID (parameter name without the leading colon)
Now the named parameter :pCID which is passed from the parent form appears as another field "CID" in this form's record as you can see when you hit the ellipsis button next to the source field and run the query.
Open the properties of the final form.
Add an additional master-slave link from CID to the data table's customer ID field in addition to the 2 existing links.
The macro setup remains untouched.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: [Solved] Multiple Entries Form With Common Fields

Post by Maax555 »

Well, there is already a customer ID associated to the sale or purchase respectively. I don't know why you store another customer ID in the table of part numbers, so I left this out for now. I'm not sure if your database is usable anyway.
Not all Part Numbers will have a Sales Ref or Purch ref depending on where the item has come from. They will always have a customerID.
I appreciate that my efforts are very amateurish but i believe it is working ok and achieves my needs thus far.
I dare say that once I have got this up and running I will rewrite it minimising the table sizes like partnumber, as you correctly point out this table far too many fields which would best be presented in separate linked table. Its been a hard learning process and as you can plainly see see i still have a long way to go just to grasp the basics.
As long as I can keep getting help I will keep trying to progress.

Thanks.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Multiple Entries Form With Common Fields

Post by Villeroy »

By default all newly created columns are optional (nulll values allowed). Nulllable columns are useful for less important additional info or with filter table fields. You should mark most of your table column as mandatory (not optional, not null), so the database engine takes care that only complete records can be stored. This can only be done when your tables have no rows or only valid rows. You can not mark a column as not nullable when it contains null entries.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: [Solved] Multiple Entries Form With Common Fields

Post by Maax555 »

Villeroy wrote:Well, there is already a customer ID associated to the sale or purchase respectively. I don't know why you store another customer ID in the table of part numbers, so I left this out for now. I'm not sure if your database is usable anyway.

How to pass the customer ID from the master form through the intermediate form forward to the data form:
Open the forms navigator.
Source of intermediate form:

Code: Select all

SELECT "Filter".*, :pCID AS "CID" FROM "Filter" WHERE "FID"=2
Link master field: INT1 (from the master record where FID=1)
Link slave field: pCID (parameter name without the leading colon)
Now the named parameter :pCID which is passed from the parent form appears as another field "CID" in this form's record as you can see when you hit the ellipsis button next to the source field and run the query.
Open the properties of the final form.
Add an additional master-slave link from CID to the data table's customer ID field in addition to the 2 existing links.
The macro setup remains untouched.
So i have attempted to enter the above as it was not immediately obvious to me. As soon as I open the form i get a pop up box requesting a value for pCID
This is the steps I took to achieve the above.

Open the forms navigator.
Source of intermediate form:

Code: Select all

SELECT "Filter".*, :pCID AS "CID" FROM "Filter" WHERE "FID"=2
[/i]
Link master field: INT1 (from the master record where FID=1)
Link slave field: pCID (parameter name without the leading colon)
[/color]
I opened the Sales_Purch_Form and changed the Content to "SELECT "Filter".*, :pCID AS "CID" FROM "Filter" WHERE "FID"=2"
I linked master INT1 with slave CID

Open the properties of the final form.
Add an additional master-slave link from CID to the data table's customer ID field in addition to the 2 existing links.

I opened the Filtered_White_Form and added a third link master CID with slave CustomerID
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Multiple Entries Form With Common Fields

Post by Maax555 »

So now made the following changes as I did not fully understand where to update in the previous post.
Added "SELECT "Filter".*, :pCID AS "CID" FROM "Filter" WHERE "FID"=2" to Filter contents form.
In the Filter Subform added Master Link INT1
Added pCID in the Slave field (this was not in the listbox as an option so just typed it in).
As soon as I open the form i get a pop up asking for pCID value, i can enter any number, press enter and the form correctly updates the fields and saves the record.
Its obvious I have still got something wrong as not expecting a pop up and can enter any number and it works.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Multiple Entries Form With Common Fields

Post by Maax555 »

Ah, i opened the Filter SQL command, unticked the visible option for pCID and looks like it may be working.
Now testing further
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Multiple Entries Form With Common Fields

Post by Villeroy »

More in depth explanations in the last form.
Attachments
Project_Tracking4.odb
(72.5 KiB) Downloaded 211 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Multiple Entries Form With Common Fields

Post by Maax555 »

Villeroy wrote:More in depth explanations in the last form.
Sorry. Have been away from this as computer failed (again) and new install of everything. I thought prior to the computer failing this was pretty much complete.
However I am now using the last example uploaded (with explanation), while the customer ID is being recorded, its always saving as a 0.
When i check my other back up files i have it working, saving the customer ID correctly but only with the undesirable saving of the form before part number or description is added. Thus if the entry is abandoned before adding this information a new record is created without part number and description.
Also I have lost the table control version which was allowing multiple entries to be entered using the same Customer, SalesREF and CustPurch.
:-(
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Post Reply