Welcome to te forum, ralfthewise,
For my understanding:
You have a Contracts Table
You have a Load Table, related to the Contracts Table by ContractNumber
So in the Load Table you may have against Contract 10001 and 10002 the following records:
Contract Number Load Number
...and you want to add another Load to Contract 10001, and call it Load 004?
I suggest you create a self-join relationship for the Load Table by ContractNumber, and set the LoadNumber field to auto-enter by calculation:
= Max (SelfByContractNumber::LoadNumber) + 1
If you create a new record in the Load Table it will auto-generate the next number as soon as you enter the Contract Number. If you make it easier for the user to create a new Load by allowing them to view an existing Contract with a portal showing all of the existing loads for that Contract (with a relationship that 'Allows creation of new records'), then it will again auto-fill the Load Number with the next one you want.
Thanks for the prompt reply. Having some problems, probably a result of my newbie status as much as anything else.
1. Self Join: I drug loads:contract number to loads::load number. It asked to create another table and did so, naming that table = Loads 3. The relationship went Many on the loads::contract number side and has a slight vertical bar on Loads3::Load Number table.
2. The "SelfBy" to which you refer in for the calculation. I have a "Self" but no "SelfBy". I'm running FMP10, am I missing something.
Thanks for your patience.
Found part of my problem. I dug further into the Type "Number" and found that you meant I should use "Auto-enter Calculation" under the Number area. I was attempting to create the Load Number field as a Type Calculation.
However, I'm still having problems with "SelfBy". Any direction?
Sorry for the lack of detail in my explanation - it's always hard to know how much people already know (so laziness makes me tend to err on typing less...)
You should set up a new relationship (File ->Manage -> Database ->Relationships tab) with the Load Table on the left and the same Load Table on the right.
The two listings below will look the same - they are both the listings of the fields in the Load Table.
In the left hand list choose the field 'ContractNumber', under the 'Operators choose "=", and in the right hand list choose 'ContrcatNumber.' This will make each record in the Load Table check through the other records in the Laod Table and find all the ones that have the same ContractNumber as itself.
You should now have a Table ('Occurance', strictly) called 'Load', and another called 'Load 4', joined with a line from 'ContractNumber' to 'ContractNumber'.
When you click 'OK' you will be prompted (probably) to accept the name for this relationship that FM suggests. It will probably be like 'Load 4'. Just accept it now - you can change it later.
In the 'Define Fields' tab go to the Load Table and the LoadNumber field and define the calculation I previously suggested - by changing the Table Occurance at the top left of the calculation dialogue to be 'Load 4' (or whatever the name was the you accepted as that new relationship name.)
Thanks for working with me on this.....I must still be missing something.
I have 2 tables:
- Table 1 "Loads 020309" with 2 Fields "Load Number" and "Contract Number". Contract number is a text field using a Value List for the possible Contracts, from which the user selects the contract via a drop down.
- Table 2, which is created when I drag Field "Contract Number" to Field "Load Number" in Table "Loads 020309", winds up being called "Loads 020311". The is a many to many relationship drawn between "Contract Number" in both tables.
- The relationship between the two tables is (Left Side) "Loads 020311 --> Contract Number" = (Right Side) "Loads 020309 --> Contract Number".
- The calculated value for "Loads020309 --> Load Number" = MAX (Loads 020311::Contract Number) + 1
The result in Table view is I can add a Contract Number via the drop down and the Loads field remains empty.
If I change 4 above to = MAX (Loads 020309::Contract Number) + 1 I select Contract Number and the Load Number becomes that Contract Number + 1. EX: Contract Number = 10001, then Load Number comes back 10002. Load number should come back 10001-1 (or -2, etc).
The field 'LoadNumber' should just be made a 'Number' field, when you defined it in Field Definitions.
But when you click through the 'Options...' button in the bottom right of the Field Definition box you will get another few tabs.
- Select the 'Auto-enter' tab, and click 'Calculated value'.
- Enter the calculation MAX (Loads 020311::Contract Number) + 1, and when you specify a Contract Number (from your value list in the field 'ContractNumber') you will see that it populates it with the correct next Load Number.
You may want to then create a concatonated field similar to:
ContractNumberLoadNumber = Contract Number & "-" & Load Number.
This will give you the text in the form 10001-1.
If you get those steps to work we can work on making it in the form you originally suggested: '10001-001' etc.
We'll get there!
PS: Strictly speaking, you have (or should have) only one 'Table' from your description. The other is another 'Occurance' of the same table: Loads 020309'. They are the same table with the same records, fields and data as each other. Filemaker uses the different 'Occurances' to let you link Tables in different ways. For example, you may want a Contracts Table to link to the Load Table by matching Contract Numbers. Or you may want it to link by matching Customer Names. When you want to make a calculation that is using a link between the two tables, Filemaker has to know which link to use - by ContractNumber, or by CustomerName. So it makes you call each 'Occurance' a different name, so you can be specific about which one you want ot use in the calculation.
PPS: I think you would be better having two Tables - one to hold one record for each Contract, ('Contracts Table') and one to hold one record for each load ('Load Table'.)
I did finally get this to work. You are the bomb.