links or lookups

[robrsla]

I'm new at this but what I want to do is be able to find data in a table for instance which clients need work done then export that find to a new table (work orders) that creates and tracks work orders, sequentially numbering them, using the client profile from the client file. IE name, address. is this a lookup? portal? what? as time goes the work order file would compile all the work done for each client.

[efen]

Something like the attached? - portals and using client details on the work order layout

work_orders.zip

[robrsla]

I probably wasn't clear. When I find my "found set" in clients table. For instance the 46 clients of 184 that need a spring cleaning. I need to "copy" those "found" clients that need work done to the work order table. I can't link the tables by client id because the work orders tables does not "know" which clients need the work done. I'm trying the copy field function, "go to client id, copy, open work orders, go to client id paste go back to client table.

once I can figure this out I have other modules to use this feature in. I think I need to use lookups but am not sure how this feature works.

lastly, once a work order is created I don't want the data to be changed, even if the client data changes in the client file.

[bcooney]

Welcome to the Forums, "robrsia".

Sounds like you are finding clients, and for that found set you wish to create a new work order for each client.

You'll need a script that loops thru the found set of clients.

In pseudo-code:

//In client found set on layout Clients

Go to record first

loop

set $clientID to ClientID

go to layout Work Order

New Record

set WorkOrder::_kF_ClientID to $clientID

go to layout Clients

Next Record, Exit after Last

end loop

Now, since you want to capture the client's info at the time of WO creation, you'll need fields in WO for the client info. These fields will be set to the entry option of lookup when a new clientID is created in WO. Lookups copy info from one table to another based on a key field, in your case the foreign ClientID.

[LaRetta]

Hi robrsla, welcome to FM Forums!

Stepping through the process, it would look something like this:

Go to your Clients table and find those Clients which need spring cleaning. Now go to a layout based upon your Work Orders. Perform an Import from target table of Clients to destination table of Work Orders. This will import records (one each from your Clients found set).

All you will need to import for SURE, is the Client ID from Clients into the Work Orders::ClientID field. [color:green]Set the import to ADD only and be sure Perform Auto-Enter is checked (this will create a new Work Order ID as they import). Now, as for whether you need to look up any of the client's information and plant it in Work Orders ... that would depend upon your business rules. Here is an example:

A client's address may change over time. If the work order needs to track which address the work was done on, then you would want to have fields in your Work Order table for the address (and set them as auto-enter Lookup or by Calculation to set the address information in your Work Order file). If it isn't necessary to keep a backward audit track of where the work took place, you can simply place the Client's address field directly on the work order table. Since they are related on ClientID, the information can just be displayed through the relationship. Another example is client's phone number - there is usually no need to keep old numbers so you can just place the client's Clients::PhoneNumber field directly on your work orders.

UPDATE: Hi Barbara, I didn't see your post but since we seem to prefer different techniques, I decided to leave mine so robsla has another option.

Also, I doubt you are using vs. 4 since you mentioned tables. Could you go to My Profile > Control Panel and FileMaker Questions under Profile Options and update your profile for us? It helps when answering questions (that is ... IF we remember to look at it.) :blush2:

Edited October 12, 2009 by Guest
Added update

[robrsla]

This is EXACTLY it!!! I just couldn't figure out the script steps. Thank you sooo much. Now I can replicate this in all my other tables and create a years work of work orders from the data in the clients file. Thank you so much. I'll keep you posted on my design.

[robrsla]

I am using version 4 for now but wanted to use the current "lingo" I'm actually using several different files. Since joining the site I have convinced the boss to upgrade to v 10 pro advanced. hopefully this will help my programming. What are your thoughts on this upgrade idea?

[LaRetta]

What are your thoughts on this upgrade idea?

The differences between vs. 4 and vs. 10 are tremendous. You could convert your files over but I would strongly suggest you re-write in vs. 10 to take full advantage of all of the enhancements (and using multiple tables). Also, there are many gotchas during a conversion such as this (Today function no longer available, True/False booleans no longer available, adding Commit Record/Requests and so forth) so a conversion (when the version gap is huge) would take more time than a re-write. And a conversion would keep them in separate files which probably isn't necessary.

You should certainly upgrade to vs. 10 and re-write your solution. :smile2:

[Lee Smith]

Hi robrsia, and welcome to the Forum,

Read the System requirements for version 10, to ensure that your office machines are compatible, as there might be some additional costs involved. Link

Lee

[IdealData]

LaRetta and Lee have given good advice here - backed up by their many years of experience and FM product knowledge. And I will share mine too...

I have been running/developing an in house solution that actually started out in FM v2 and has been upgraded all the way to FM 9 and hosted on FMS 10. The biggest single leap I made was from FM6 to FM8 - and discovered all those "gotchas" that LaRetta points out. This process alone took 3 months to plan and 12 months to bug fix - however I had no option as the application comprised over 40 files with 30 users in continuous daily use. There are nearly 5,000 scripts and 5,000 fields and around 1,300 layouts - so a re-write was out of the question.

If you want to find out if you can convert (easily) and then start re-writing then you could download the FM10 trial and see what you get. This will at least give you an idea of what might be involved.

Lee's point about hardware (and Operating System issues) is particularly valid as even your current Windows 2000 machine will not run FM10.

Edited October 14, 2009 by Guest

[robrsla]

Thanks for the tips there. I'm going to go ahead with upgrading to v 10 pro before I get to far along. Secondly. we are using mostly vista machines with one older xp so things should work ok but our server is linix. I don't see it specifically on the FM site but it should work fine, RIGHT??

And one more thing. I'm a bit confused about files v. tables. What;s the difference?

my plan (working in v4) was to create up to 14 different files that are all related. (the Files are all similar in content like containing scheduled work etc so using seperate files allows me to use the same field names like, jan feb mar, for instance. I can't do that if I use one file with 14 layouts without having "jan1" "jan2" "jan3" etc. which will get confusing plus each file contains unique data too. )

Here's the question, In the responses to my script question it looks like the respondants referred to one file containing several tables. Is this the same as having multiple files.? are tabel fields unique to the table?

The work order file has to relate to multiple files (or tabels) and draw scheduled work from each of them. example, our companny does turf care (one file), pond andfish care(second file), plowing and snow removal(third file), IPM (fourth file. etc etc. Each of those files contain menus of work options (tables). Is this right?

if so how then do I "call" workorders in the script?

IE CLIENTS

Find (find set) Clients that need their pond checked in august)

28 found

go to record (first)

set variable ( client_id)

[color:red]now what??

??[Go to Work order File]?? how

go to field (client_id)

set variable (client)id to client_id)

return to client file

go to record next

set variable...... loop this???

The sturcture of the company is 10 different work type groups, each containg their own details and one master work order file that will pull work for each of them and sequentially number and track the workorders. Before I get to far into design I want to make sure my logic is right.

Thanks for all the help as a newbie it's all very exciting.

[robrsla]

If I use this import method over a script how then can I deliniate which file to import from? I need to import from different files at different times. Is there a script command for import that I could set variable to?

[LaRetta]

the Files are all similar in content like containing scheduled work etc so using seperate files allows me to use the same field names like, jan feb mar, for instance. I can't do that if I use one file with 14 layouts without having "jan1" "jan2" "jan3" etc. which will get confusing plus each file contains unique data too. )

I suggest you stop and review your structure now. You want all those 'similar files' in one file and THE SAME TABLE. You would just differentiate them by Type or Date or Category or whatever else you need. What you describe is the number one mistake made in this business (splitting data out when it should be combined and combining data when it should be split). Jan, Feb etc should NEVER be field names either. When you have 'like' data, it should be related records instead of fields.

As for the import script, it was based upon taking the ClientID from ONE table (Clients) and importing it into Work Orders (assigning the ClientID to a new work order). If you import from different files, how will you know the proper ClientID to assign?

I really can't advise except to highly suggest that you get input on a proper relational structure which normalizes your data - draw it out on paper first. You can always post what you have and get input from the forum. :smile2:

[robrsla]

where can I find more information on proper relational structure? I really want to set this up right.

I was using jan feb mar as fields based on when we do work. for instance I want to find all clients the get x done in july. I made july a radio button that got marked yes/no when the work was contracted. etc. whats the proper way to set this up?

[LaRetta]

where can I find more information on proper relational structure? I really want to set this up right.

You can search web for 'normalizing data' and 'relational theory' and there is a wealth of information. Here are two which might be helpful:

Whitepaper for FM novices

Key Concepts

There are many FileMaker books as well.

I was using jan feb mar as fields based on when we do work. for instance I want to find all clients the get x done in july. I made july a radio button that got marked yes/no when the work was contracted. etc. whats the proper way to set this up?

Again, it would depend upon your structure. You have said several things which send off red flares that you are not (and will not) structure correctly. If you are willing to pay a Developer (and no I'm not suggesting me), I would suggest you investing in one at this beginning stage. Once your base structure is correct, the rest will flow naturally ... if you structure wrong to begin with, you will constantly be fighting yourself.

Can you post an empty clone of what you have so we can advise?

[robrsla]

What are the price points for this type of help and if not you, who would I use. I'm in a remote area. Not a lot around. a web course of web based contract would have to do. I think it's very important I get this off the ground correctly. if not like you said I'll be fighting myself, and so will everyone else that wants to use the data. What I have now is a mess bucause I've been jumping around with different concepts. Plus I don't know how to post it. (i guess I could figure it out. ) What I don't get is the difference between using tables in one file versus multiple files. I downloaded a business solutions demo from FM site and it uses seperate files.

can I pay you to correct my structure? do we use email instead of the forum?

I'm reading the white paper you suggested and it seems it will be very helpful .

[LaRetta]

What I don't get is the difference between using tables in one file versus multiple files.

Since vs. 7, 'files' can be stored within files (and they are called tables). It makes it much easier to work with them (for scripting, privileges and layout work) if you have all your 'files' in one file.

I downloaded a business solutions demo from FM site and it uses seperate files.

This is because FM simply migrated their files instead of recreating them to take advantage of tables.

I would first suggest posting what you have here (place them in a folder and zip them). There are developers here who may have the time to take a look (including myself) for no cost but no guarantees. Usually, we can spot the major problems immediately ... here are a few:

1) Any time you have multiple 'like' fields within a file table, it usually means those fields should be records instead, for instance, Jan06, Feb06, March06 or 2007, 2008, 2009 etc.

2) Rarely should you need to enter the same data twice. In other words, you should be placing your ClientID key in Work Orders and displaying the data through the relationship instead of entering the client name in work orders again.

3) You really shouldn't create records until you need to use them (you mentioned creating a year's worth of work orders ... this sends red flags).

Understanding the basics of relational design should only take few hours of review. Providing detailed assistance to be sure you are structurally correct is more involved; because it involves knowing your business and data more deeply.

If you can't or don't want to provide your information for our review, you can hire a developer by several ways: 1) Post here on FM Forums Services wanted forum , 2) Find FileMaker Developers, or 3) ask around or read FM Forums and find developers you seen to trust.

If you can, start with posting your file. I will certainly give my input on the base structure.

UPDATE: BTW, when developers assist or design, they rarely need to be at the location. It all can be handled either by passing files back and forth or working remotely.

Edited October 16, 2009 by Guest

[IdealData]

but our server is linix. I don't see it specifically on the FM site but it should work fine, RIGHT?

No. Linux is NOT supported.

[robrsla]

After readign the white paper you referred me to I decided to scrap the files I had and start fresh after following the recommendations I've recieved. I will keep you posted certainly.

You have helped me spot several flaws, IE using a client type as a field instead of having a field client type then using the actual type code as the data, Thank you.

I also was starting at the wrong point and now understand where to start better.

My reference to a years worth was not that I would create work orders a year out but be able to update them over the course of the year to keep a historical record.

We'll be creating them on a daily, weekly and monthly basis.

Retooling my client file should I break out their data like billing address and site address into tables?

Where I am now is a client dB which will have avaiable in related tables all the types of services we provide (17) each of those tables will contain the data for that specific service. I will try to complete one as an example and post it.

I'm using data imported from an old spreadsheet as sample date so I can see how it's processesed when I do things. It's not the end user data.

Learning slowly, Thanks

[LaRetta]

Retooling my client file should I break out their data like billing address and site address into tables?

You have hit on a very important question and only one you can answer (as I indicated that more advanced structural decisions must be business decisions and more information about the business and data would be necessary). But here is a general perception (in my opinion) and addresses and numbers is the perfect example:

ADDRESSES:

If you are ABSOLUTELY SURE that you will NEVER have more than one or two addresses (home and office), you could put them in the same table. But I can almost guarantee that you will, at some time, have the Owner come to you and say they need a third because this customer has a winter home in Florida ... oh dear. Then you'll need to add more fields ... and more ... or adjust your structure.

NUMBERS:

Your customers will usually have many numbers. You can use one table with only a Type (phone, fax etc) and Category (office, home, warehouse). How you organize this information depends upon your business rules and needs.

In general, if you use records whenever you have 'multiple' possibilities, you will be safe. It is far easier to build in expansion and not need it, than to not be able to expand and need to adjust your solution. Now there are limits as well ... if you have two 'like' fields, such as Name (legal name and alias), you probably wouldn't need a related table to hold names. But, ROFLMAO, you'd probably find someone with 15 names as well. In which case, I'd keep two fields for names (and just add the other names as comments (or within the alias field). Then you only place the table's unique ID (such as CustomerID, Work OrderID etc) in the other tables to relate them and pull the information through (display them) when needed. This saves having redundant data throughout your system which is 1) space hogging and 2) difficult to keep in sych.

All of these questions should be worked through. Using paper helps you group into modules. Keep the 1:n concept in mind (one-to-many) and split when you think you'll have multiple 'whatevers' to the single 'main' entity. Let us know as you have questions or can use clarification. A good base in a solution is just as critical as a good base on a house and there are many wonderful people here willing to help (as they have time) who've spent decades studying this stuff.

UPDATE: I notice you asked if the addresses should be in separate table[color:green]s. The answer is of course, no, there should be only one Addresses table with a Type field (indicating home, site, mailing, shipping, warehouse) and so forth.

Edited October 16, 2009 by Guest
Added update

[bcooney]

"Where I am now is a client dB which will have avaiable in related tables all the types of services we provide (17) each of those tables will contain the data for that specific service."

Can you have a table of services with 17 records?

[robrsla]

I don't think so because we are a company that offers 17 different services of which a client can retain us for 1 or all 17 or any combination therein. Within each service there are sub tables if you will of work to be performed for that service. As the hub of my design (solution) I'm using clients file and each client has a client ID #. from there I'm attempting to design the 17 tables (I'm attacking them one at a time, easiest to hardest) related via client ID that breaks down the service features. For instance we do pruning. A client can retain us to do tree pruning, ornamental (shrub) pruning and/or hedge pruning. I can either create a prunning table related to the clientID and break that down to pruning types or I can create 3 pruning tables that handle the specifics of those 3 different services.

Bottom line is I'm a mess and should hire a programmer to help me get this sturctured correctly before moving forward. the info here is great but I''m well out of my element.

any suggestions.

[LaRetta]

17 different services of which a client can retain us for 1 or all 17 or any combination therein.

Many services - one client ... many sub-services - one service. I agree with Barbara - one services table with the service Type (one for each 17). You would also have sub-types which relate to the Services table. :

[bcooney]

"Bottom line is I'm a mess and should hire a programmer to help me get this sturctured correctly before moving forward. the info here is great but I'm well out of my element. "

Recognizing that a correct data model is essential is a huge accomplishment. Novice FM users often just build without thinking thru the data model. Then, down the road, they are posting requests to solve their data model problems, often leading to convoluted techniques. I shudder to think what their Relationship Graph looks like.

LaRetta and I will certainly be here to help you with the data model. However, perhaps 3hrs with a local developer to diagram your data model will be well worth it.

[Fenton]

There is another possibility, proposed by David Graham. It consists of a central table for the fields which belong to ALL types, then separate tables for each type (which requires it). It gives you the flexibility to see the services/items all together in one place when required (scheduling, invoicing), and as separate when necessary (work orders with details).

It is not really for beginners. Though it is not all that difficult to implement, once you understand the concept. But if I were you, I would try hard to first try and do the services within the same table, with Categories and Subcategories to separate the items. You can then build self-relationships, value lists based on the self-relationships, portals built using a combo of the two.

I once built a solution for my landscaping business (years ago), with over 200 items separated that way, including "bundles" (several items which are used together as one item). We were only "design/build", with very little maintenance; so it was simpler than yours.

It is OK to have fields that only apply to one type or the other. I would prefix such (very short abbreviation), so they sort together. Eventually you may come to a point where you realize, "this is just too much, too messy, I must separate these into tables." Then it's time to look at the Graham method. But you must know what fields can be "shared" (central table) and those which cannot (separate tables).*

The trouble with the separate tables and no central table (what you have now) is that there is no central place to target for things like scheduling and invoices. You could copy things to an invoicing table, but then items are somewhat disconnected. It precludes such things as dynamic job-costing (to answer the questions, "How much time have we spent on this whole project? How far over budget are we likely to be? Which items are the worst?")

I would offer to help. But I'm a little busy right now; though that could change at any time. In any case, the first step in this project is for you to get a very clear idea of what things are (or can be) common to all types. Think abstractly if possible.

*Or, since you already have separate tables, you could try the Graham method now (with a renamed copy of your solution).** Try and see how many fields you can put into a central table that work for all types. Then see how many are left.

**By "renamed" I mean using FileMaker Pro Advanced Tools, Developer Utilities. You really need FileMaker Pro Advanced to be be a developer. Even beginning developers would benefit greatly from its additional tools.

[robrsla]

I don't know if you recall trying to help me. But a while back you gave me some good advice, basically Do some reading/ learning. THANK YOU! after reading your recommendations and doing some research I came up with the following solution.

Keep my contact table and create a project table which relates to a task table. each of our services is now a project type and the corresponding task or tasks are selected from a huge list of services we offer so we can mix and match each service to a project. each project can now be sorted by type or scheduled date or by client and the work orders I was aching over are created as a report of the finds. within the solution. I went form 19 tables to 4 and its all looking pretty good.

I reached this point to heeding your advice and rethinking my problem. thus finding a programmer type solution. It all works really really well. NOw I'm polishing it and was wondering if I could use my unique client ID field from the clients table as a pull down list in my project file. this field is currently the relationship field between the two.

I can't thank you enough for the advice. I was way off track.

THanks

PS

I figured it out. use a value list as defined through the relationship and cust ID field. It works beautify.

I'm scaring myself/ but really enjoying this challenge.

Edited December 11, 2009 by Guest

[robrsla]

Without your past advice I'd be lost forever. I've upgraded to pro 10 and found a solution that's working great. Now what I'm looking to do is use the client ID field (which links the clients table to the projects table ) as a pull down menu in the project table.

I'm using a projects table as the hub of my solution, instead of the clients where I was, and as I'm preparing a project (also linked to a tasks table) I'd like to be able to use the Client ID field as a pull down list from the available clients. Right now I look up the clients ID # on a print out and type in. My clients info comes across and all the data comes together in that work orders table that was giving me such trouble. Is there a way to have the client ID field from the client table available as a pull down in the projects. Right now this # also defines the relationship.

Never Mind I got it. use a value list defined from the relationship and field

Edited December 11, 2009 by Guest

[bcooney]

If the client popup is too long, then you could use a popup list selector. http://fmforums.com/forum/showpost.php?post/319817/

[robrsla]

That looks really cool but I'm not sure how to work it. (i'm not to bright) I have another question. As I mentioned, I have a client table and a project table linked by ID's all works well between then and I'm finding ways to do what I am looking to do. Here's one function I've set up.

I placed a pull down portal (projects) on the client form which lists all the projects by name for that specific client. so when I look up/ (find) a client I can also see all the projects we have done there and or are scheduled to be done there via the portal.

now here's what I'd like to do. I would like to be able to then click on any of the projects in the portal and be taken to that specific project on the projects table. (each project has a unique identifying number "project_ID") Is this possible? I'm thinking I need to nest a button in the portal which "goes to"

am I on the right track?

how can I use the popup select demo you sent me

for the client id pull down ?

Thanks for your help.

[bcooney]

Yes, you would put a button on the portal row that goes to related Project. I've attached a demo from another question that includes a gtrr button in the portal.

As for the clientID. You've seen that the using a popup menu based on a value list of ClientID ClientName can become too unwieldy. Instead, put a Select button next to the related Client Name field. The select button would start the Selector routine. At the end of the select routine, the Project::ClientID is set.

Another option is to only allow new Project records to be created from the Client Form.

Aircraft.fp7.zip

[robrsla]

The portal button worked great. Thing are really starting to come together. But everytime I show the boss whats been done he asks for MORE. (typical right) We number our work orders sequentially and I have a Work order field that fills in each time a new record is created. What I've been thinking about is this. Our work orders are numbered 09-XXX for this year. Next year they will be numbered 10-XXX is there a way I could take the last two digits of the creation date and use it as the prefix to the work order? then each year we could start over at 1 for the work orders numbers with the prefix of the current year.

I'm thinking a calculation for the work order # but its over my head.

thanks

[bcooney]

NO! Stop right there. Keys fields are meaningless!

If you want a work order display ID that is NOT used as a key field, then go for it.

No time for the calc right now, but it'll use Year ( ) and Get (Current Date) and an auto-entered serial. Resetting to 1 every year is a bit of a trick.

(OK, LaRetta and Comment...chime in please!)

[robrsla]

My solution crashed and burned. pieces worked well but the interface was amateurish. I downloaded from Fm site a solution called business productivity solutions. It has several modules that are very close to what we are looking for and includes a very pleasing interface. Then I tried to modify it to be more in tune with our needs. I can't seem to get the relationships right. Example I want to jump back n forth between related projects and their tasks and the clients data in contacts table. and can't seem to do it. I used "go to related record" but every time it executes I get a new client window or I get table is not available. are you available for consultation.