• NewB
  • New member
  • Offline
  • Registered: 2021-08-21
  • Posts: 2

Topic: Is it possible to have a search from form1 find the result on form2

Sorry to be a pain but I have run into two problems

1. Is it possible to have a search from form1 find the result on form2 to and return the value back to form1? I have a date search that I need to find name, finish time and shift notes of person on for a specific shift.

2. I have rosters that will be uploaded as CSV file with persons, name , shift dates and times. I created a table called staff to add persons name and photo. When I check the roster it seems that the photo is not picked up and that I need to add the photo again to every person and for every shift and roster. How do I link each photo to correct person.

Post's attachments

Attachment icon Roster.zip 489.27 kb, 218 downloads since 2021-08-21 

2 Reply by derek (edited by derek 2021-08-21 17:12:27)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,675

Re: Is it possible to have a search from form1 find the result on form2

Hi,
Had a quick look at your project and I'd make the following comments;
1.  Rather than start with Form1 and then call FormRoster and then have to go back to Form1, wny not just start with Formroster.  The results of your filtering are done 'in situ' on FormRoster and then you go to Form1 to view a specific record.
2.  In your database schema, you are holding photo on both the 'staff' and the 'roster' table.  It's unnecessary to have it on the 'roster' table.
3.  Holding a photo actually inside the roster.tablegrid1  might not work too well - you tend to find that, in order for the photo to be recognisable, you have to increase the tablegrid row height to the point where you can only get 4-5 records per screen.  As an option, you could try displaying it as a separate image that shows when you click on a row in the grid (see attachment).
4.  When you perform your .csv import, you are not providing the key to the staff table (see screenshot.jpg in the attachment);  this is why the photo isn't being displayed on form1.  Just to get it working, I'm calling it from the 'staff' table using the 'firstname' field as a link but using a unique key would be preferable.
5.  I've changed the format of the time columns in the tablegrid so that it only shows hours and minutes.
The above requires a small script to be written - some of which is cosmetic (like the format of the time column) and most of the rest which wouldn't be needed if your .csv file held the keys to the 'staff' table.
MVD is extremely flexible and there are plenty of other approaches but hopefully some of the above will help move you on.
Derek.

Post's attachments

Attachment icon Roster2.zip 917.97 kb, 234 downloads since 2021-08-21 

  • NewB
  • New member
  • Offline
  • Registered: 2021-08-21
  • Posts: 2

Re: Is it possible to have a search from form1 find the result on form2

Derek, thank you I really like what you have done, and for taking the time and going into detail as to what I could do, and for adding the explanation notes on the script this will help me with  future DB builds. 

I was planning on hiding any buttons that would allow the roster to be edited.as rosters will be imported by csv
I have reattached again and tried with a hidden Tablegrid on form1. I don't know if I can have the Datepicker select first row and click on line with out using mouse to pull the required data, and keeping the Roster tables more like a backend?  and if this can be done I have other projects that this could be implemented on.   
But you guys are the experts so I'm happy to go with your recommendations.

Regarding ID_Staff key I have edited your Screenshot or is the ID supposed to be Unique?
Thanks for your help

Post's attachments

Attachment icon Roster.zip 489.27 kb, 204 downloads since 2021-08-23 

4 Reply by derek (edited by derek 2021-08-24 10:43:58)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,675

Re: Is it possible to have a search from form1 find the result on form2

Hi,
Again, I'd suggest doing it the other way around. 
Instead of keeping the roster table as a 'backend', I'd have it as the starting point for everything and then you just apply a combination of filters to reduce the visible rows to just the data you want to see.
If I understand you correctly, you only want to be able to view the information that has been upload from .csv files and that you also want to be able to just 'up' and 'down' arrow (or single mouse click) on any row of data to see all of the information. 
In that case, perhaps think about doing it all on one form.  It's up to you how much information to show in the tablegrid and how much to show outside of the grid - in the attachment, the tablegrid shows names, start and end dates and times and the photo and notes (information that is usually too big to display in the grid) is displayed outside of it (but it's just a suggestion).  As you scroll up and down (or mouse click) the information that is too big to display changes accordingly.
I have put a 'data' button on the roster screen which takes you to a screen which is literally a dump of all your .csv data.  I've yet to come across a scenario when someone at some stage doesn't mess something up and you need some sort of recovery option (but it can be removed if you don't think it's ever going to be used).
With regard to keys and tables - yes, the ID must be unique;  MVD maintains all keys to a table automatically (called a primary key).  When you link two tables in a relationship (such as staff - roster), it also maintains the link between the two tables by using a foreign key on one of the tables  (which is the primary key of the table to which it is relating).
In your .csv file, the foreign keys that link 'roster' to 'staff' are missing but what we are able to do instead (although it's less efficient) is use 'firstname' which appears in both tables, as a link.   But you would need to ensure that the firstname is held identically in your 'staff' table and your .csv file (it will be obvious if there is a mismatch because the photo on the 'roster' form will be blank).
Sorry for the long-winded answer but have a look at the attachment and hopefully it will give you some ideas and just ask if something's not clear.
Derek.

Post's attachments

Attachment icon roster2a.zip 575.87 kb, 282 downloads since 2021-08-23