Using TO's and relationships
I have hit a bit of a snag in a new program I am developing. Up until now I have been able to make my programs work without using many TO's and my relationship graphs have been very basic. But now I am working on a program that I feel could work well with TO's but I am still usure when it comes to working with TO's, also I need to pick up my game if I am to develop better programs.
The issue I have is that I am trying to develop a stock management system for my client. So I have an inventory table that I will use to capture all the different Stock Items, and I need to create a layout for the users to book stock in and out of the system. Now I have created a Stock table which I was thinking of using for the booking in and out of stock. What I did was duplicate the Inventory table so that I could get the same field names and then added fields like Booked In ( going to be used to set whether the stock is in or out) and other fields like the date and time it was booked out/in, quantity to be booked out and so on.
What I was planning on doing was creating 2 tables, one for booking in and one for booking out, but I have been going over training video's and I believe this to be the wrong method. I want to make a proper FM program this time around and I feel that maybe this is where TO's will come into play. What I started out doing was to create a layout based on Inventory and then use a portal to the stock table. The worry I have is that I don't really want the Inventory table adding records unless it is new stock items that are being added. So my initial thought about using the Inventory as the layout table just seems wrong.
Now with regards to TO's, and this is where I get stuck . I was thinking of creating 2 TO's based off the Stock table and connect them as what I think would be a self join to the Stock Table, and have allow record create on the Stock Table side, seeing as that is the table I was thinking of using as the portal table. Cause then the 2 new TO's would be the base tables for the layouts and I would have a portal which would be the main Stock table that the TO's are joined to. Am I correct so far in my thinking? Or do I have it the wrong way around and the Stock table be the table the layouts are based on and the portals be the 2 new TO's? Cause once I have that right, I was thinking of then using a field to store a value, something like, yes or no, on or off, 1 or 2 and that would then get stored per line item/record on the portal, and show if the stock is still out or come back. I could also then use that field when searching for who has what stock outstanding, or who needs to get reminded they have stock out and it need to be returned in x amounts of days, or whatever the criteria would be.
One of the other issues I think there might be is, how do I recall which line item in the portal goes with which order? I thought of using a field for example, job number, on the layout outside the portal, which could then be copied using Set Var, to the line item in the portal when each portal record is created.
Am I on the right track here? I have to get this right and start creating FM programs that follow proper FM techniques. I think I am starting to understand TO's a bit more, it's just now figuring out what would be the field to do the self join to, if this is the right way. Any guidance in this would be greatly appreciated.
Oh yes, I have a main table that I use to get certian info before the book in/out layotus are navigated to, and both Inventory and Stock are joined to this table. I also have the Users table joined to this main table and in future, when I add more tables, I was going to use this main table as a type of central table in the relationship graph. That main table is the table that the home screen is based off and the home screen is where the user sets there name and can navigate to layouts like adding or viewing users, create or view stock and go to the book in/out layouts, and in the future any other laytous the users are going to need to go to. This will change a bit as the program develops and I add in things like login accounts for the users, but until then, the users will be selecting their name from a drop down list. I'm not sure if that info is neccesary to say, I just thought it might add to the mix. Also it would show that there is a link between Inventory and Stock.
Thanx in advance