Multiple Entries Form With Common Fields
Re: [Solved] Multiple Entries Form With Common Fields
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.
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
LibreOffice 6.1.4.2 on Windows 7
Re: [Solved] Multiple Entries Form With Common Fields
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:
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.
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 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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Multiple Entries Form With Common Fields
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.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.
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
LibreOffice 6.1.4.2 on Windows 7
Re: [Solved] Multiple Entries Form With Common Fields
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Multiple Entries Form With Common Fields
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 pCIDVilleroy 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:Link master field: INT1 (from the master record where FID=1)Code: Select all
SELECT "Filter".*, :pCID AS "CID" FROM "Filter" WHERE "FID"=2
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.
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
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
LibreOffice 6.1.4.2 on Windows 7
Re: Multiple Entries Form With Common Fields
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.
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
LibreOffice 6.1.4.2 on Windows 7
Re: Multiple Entries Form With Common Fields
Ah, i opened the Filter SQL command, unticked the visible option for pCID and looks like it may be working.
Now testing further
Now testing further
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
LibreOffice 6.1.4.2 on Windows 7
Re: Multiple Entries Form With Common Fields
More in depth explanations in the last form.
- Attachments
-
- Project_Tracking4.odb
- (72.5 KiB) Downloaded 232 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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Multiple Entries Form With Common Fields
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.Villeroy wrote:More in depth explanations in the last form.
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
LibreOffice 6.1.4.2 on Windows 7