I am a contractor and would like to be able to have a separate address for each project. I may have a contact with 80 different project sites, and some with the same contact address.
I set up the street, city, state, & zip in the Project> overview area, but haven't given them any field names.

Could someone help with setting this up in what table > field??

I would like to be able to put the address in the "ship to:" area on the printed part of the invoice.

Thanks again
Con

Hi Con
I'll qualify my reply by saying that I'm still fairly new to the seedcode solution and self-taught in filemaker, so the more experienced here might have a better solution - but if I understand your requirement right, here's how I'd do it:

Create a new relationship between the project table and address table:
ProjectID_kprime = AddressID_kprime
This will allow you to create an address that is exclusive to that project, and doesn't rely on a particular contact or company to link the address.

Then, your address fields that you have placed in your project layout can actually be pointed to the address fields in the address table (so all addresses, whether they are linked to users, contact, projects, whatever, are all in one place and selected using the AddressID_kprime field)

In your project layout, create a new portal that shows just one records from the address table, and add the address fields to it.

That will now have created a record in the address table that is linked to a particular project.

Now, to replace the 'ship-to' field with your project address:

There is already a relationship between the invoices table and the projects table (call InvoiceProject) - create a new table instance of the address table and call it something like InvoiceProjectAddress.
Create the ralationship between the project ID and the address ID as you did in step 1, and you will now be able to add the address data to you invoice layout.
When you browse the record, it will (I think!), show the address data that relates to the project code that you have entered into the invoice itself.

I've tried a quick dry-run on my copy of complete, and this seems to work, but as I say, I'm fairly new to this, so I may have missed something, or there may well be a much better route, but hopefully this might help you to make a start.

Good luck!

Thanks mattfenton
Looks good so far.

I have one problem:
When I put the field in the portal I can't see it or mouse on to the fields.


What am I doing wrong????????

Con

I've had this a few times, think it might be a quirk with FM9

In layout mode, select the field that's giving you problems, then, in the format menu, select field/control -> behavior (shortcut is alt-command-k on the mac, not sure of the windows equivalent) and make sure that the checkbox for "allow field to be entered in browse mode" is selected.

The other thing it could possibly be - check the relationship for the portal field and make sure that the checkbox for "allow creation of related records" is ticked for the address side of the relationship - it might be that filemaker isn't allowing you to access the field if that action isn't allowed I think.

Let me know how you get on...

cheers, Matt

Thanks again Matt; that did work.

Question:
If you go to Invoice > (left side) Address > and go down to ship to, can I get the fields to work with the project address????


Thanks... Con

There are (at least) 2 ways to do it, depending on the outcome you want:

If you wanted the address to be updated automatically, you could use a portal with 1 instance of the address table, linked by the ProjectID as you've already done on the project layout.
eg. You create a project address at Plot1, Some Street and link it to Project52 - the portal will show that in any invoices that relate to Project52

At some point during the life of the project, the project address changes, perhaps to BrandNewBuiding, 204 Some Street

All invoices (new or existing) will show the updated project address.


Or, you might prefer to have the invoice address "fixed" at the point of creating the invoice - I would do this be creating a field in the invoices table for each address field you use eg, ProjectAdd1, ProjectAdd2, ProjectCity etc and set each of these fields to "lookup" data from their corresponding field in the address table using the relationship ProjectID = AddressID. In the second version, the address is permanently "fixed" and is stored in the invoice table along with the rest of the invoice data.
Using ther above example, any existing invoices would be addressed to Plot1, and any new invoices would be addressed to BrandNewBuilding.

This is how the current invoice fields for billing and shipping are already done, so you could copy them as a guideand simply change the calculation's destination.

Hope that makes sense

Cheers, Matt

Thanks Matt for your time.

I just can't seem to get it.

With the address fields that I entered in the project layout, I would like the address fields in the invoice layout to be filled in when you select the project name. I would also like the fields to fill in the "Print - Invoice" layout on the right side (ship to).
I would also like the map button in the project layout to go to google and show the address of the project so it could be printed.

Any other stabs at this I would appreciate it.

Sorry for being such a "Newbie" but I am so excited about how much this program can do.

Thanks

Con

Hi Con - no need to apologise, I'm also something of a newbie, so this is actually making me think about the app in a different way - will no doubt come in useful as I'm only about 1/2 way through developing calendar complete into our new solution for our own use...


I think I understand what you want, and I think this might do the trick:

You've already linked projectID = addressID to make a relationship called something like InvoiceProjectAddress and made the portal in the projects layout, so that has created an address record linked to the project

Now, in the invoice table, you will find a set of fields called InvAddressBilltoStreet, InvAddressBilltoCity etc. Duplicate one of the sets (either InvAddressBillto or InvAddressShipto) and rename your set something like InvAddressProject or whatever makes sense for you.

Edit each of the fields as you go and you will see that they are set to auto input calculated data by looking up the relevant record in the address table. Hit the "specify" button to alter the relationship:

eg InvAddressShiptoStreet is currently entering data from the AddressStreet field in the addresses table, so simply change the drop down menu top left to select your InvoiceProjectAddress table occurance, and you will see the same set or address fields, but this time related to the invoice by the projectID instead of the contactID

The calculation window will then show
InvoiceProjectAddress::AddressStreet
instead of
InvAddressShipto::AddressStreet

Do this for each of the new address field you created

In the Invoice layout, address tab, select the shipping address set and duplicate them, then double click each field in turn and re-point them to your new field set (eg InvAddressShiptoStreet becomes InvAddressProjectStreet etc)

You can also do the same in the print-invoice layout - either duplicate or replace the shipping address with your new Project Address set.

Hope that makes sense, I'm not sure that I'm explaining myself very well, so feel free to ask more questions!

BTW - will have to have a think about the map button, should be very simple, but need to take a look at the script - anyone else already tried this?


Matt

Is this what my relationship should look like???

Hi Con - just read back through my posts, and don't think I've written them very well, so I'm not surprised you're a bit confused!

I think I'm going to start from the beginning and try to make it a bit easier to understand - I don't think you'll need to redo everything, but it might help...

BTW - I apolgise in advance if this a bit of 'teaching you to suck eggs', but I'm trying to list eveything in order - please don't take offence if this is a bit basic...


Start by opening the relationships dialogue (file menu -> manage -> relationships) and create the relationship that you've alreay shown in your image by creating a new occurance of the address table (I would call this ProjectAddress) and relate ProjectID_kprime=AddressID_kprime. Make sure that the address side of the relationship is set to "allow creation of related records" (double click the '=' sign in the relationship link to get to the dialogue box).

Go to the project screen and enter layout mode. Decide where you want to put the address fields and navigate to that tab.
Create your 6 address fields and link them to the address table using the relationship we've just created (when you place the field, in the dialogue that appears, change the drop down which will read "projects" to read "ProjectAddress", then select your field, starting with AddressStreet and repeat for each field in the address block (6 in total).

Now, when you create a new project, you can input a project address that is linked to that project,

Now to link the Project, Address & Invoices together so that the project address can be displayed on invoices.

Go back to the relationships dialogue and find the existing relationship Projects -> ProjectInvoices, which links projects to invoices using the relationship ProjectID_kprime=InvProjectID_kf. Create a new instance of the address table (call it something like ProjectAddressInvoices) and create the relationship InvProjectID_kf=AddressID_kprime. That now links Invoices, Projects and Adrresses using the ProjectRef as the constant.

In the same dialogue box, select the fields tab (saving any changes) and select the Invoices table in the drop down menu.
You will see two sets of Address fields (Shipping and Billing). Duplicate one set and rename them - eg InvAddressShipToStreet duplicates to InvAddressProjectStreet. As you duplicate each record, click the options button, and specify a new calculated value of the same field, but by your new relationship ProectAddressInvoices.
Eg. InvoiceAddrShipTo::AddressStreet becomes ProjectAddressInvoices::AddressStreet.
When you get to the field InvAddressShipToCompleteCalc you will need to carefully go through the calculation and change each reference to your new ProectAddress fields.

You have now created a lookup that will store the current (at the time of creation) project address in the invoices table so that you can include it on youre printed invoices.

To print the new address, navigate to the Print-Invoice layout and go to layout mode. Double click the 'ship to' address field and replace it with the ProjectAddressComplete field.

And, (I think!) that will achieve what you want it to do.

Let me know how it goes...


Matt

Matt
Thanks again.

I still have some relationship wrong. This is the relationship error that I got.



This is my Project Relationship.




Any Help, thanks

Con

You need to evaluate the calc from the context of ProjectInvoices instead of Invoice.

Hi Con
BigSox is dead right - in the specifiy calculation dialogue box in your posting the top line say "evaluate this calculation from the context of: Invoice" - change the drop down so that Invoices becomes ProjectInvoice and select your address field from that list

Nearly there!

Matt

Thanks to the both of you for all of the time and help. I feel that I am just about there.

Have two parts that I don't know how to do.....





Could you walk me though this.....




I need help with this....



The above address fields are the ones I want to see when I select the project name.

I hope some day I will be able to help Newbies like the help I am getting from this forum

Many Thanks Again

Con

Hi Con,
I'll take those in reverse order if that's OK (start with the easiest one first!)

Where you have changed to Project address, and the fields read <Unrelated Table> - simply go to layout mode and double click each field in turn.

When the dialogue box comes up, leave the selected field as it is (eg Address2 or City etc) but change the drop down in the top right (it probably read AddressShippingAddress or something similar. Change that dropdown to read InvoiceProjectAdrress (or whatever you called you relationship)

Repeat for each one and return to browse mode - instead of the error, you should see ampty fields, which are replaced with address data when you select a project to link to the invoice.


For the second part I *think* you can ignore this - it is used (someone feel free to correct me if I'm wrong...) to create a mailing label from the address data - since you presumably won't be sending invoices to the project address, but rather to the clients billing address you won't need to worry about this?


The first part seems very complicated, but it's actually quite simple - what the script is doing is pulling in data that already exists (in this case the shipping address data in the invoice at hand) and formatting it into a block, just as you would do if you were typing an envelope or label in a word processor. It then stores this as a single field to use later. What you need to do is go through the script line by line and change any reference to a shipping address field to your new project address field.

eg - on Line 6, "InvAddressShipToIncludeName" relates to the little checkbox to the right of you address block - if you haven't already duplicated this field, do so, and then change Line 6 to your new field, which I would probably call "InvAddressProjectIncludeName", but you can use whatever makes sense to you.

Continue through, changing all the references to shipping address fields to you new project address fields and it should all look good - you can test this easily by creating a test record and poulate it with three addresses - eg ShipAddress1, BillAddress1 and ProjectAddress1 - that way you can look at a test invoice and clearly see if you fields are working the way you want them to.


Hope that helps!