Creating New Contact from Company Table

[BeginnerForever]

I have a db with two tables: 1) Company_info 2) Contact_info.  I would like the ability to create new contacts while in the Company_info table.  I set up a popover button in the Company_info table to do that,  but it only creates a new record in the Contact_info table without any related data.  When using it to create a new record in the Contact_info table I can't figure out how to make it copy over the "Company_name" field to the new record in the Contact_info table.  The two tables were set up with a one to many relationship connecting the two IDs. (The first ID value is "Comp0001" for the Company table and "Con0001" for the Contacts table).

All attempts to use "Insert Calculated Result" to move over the data in the "Company_name" have failed.

[webko]

I would use a Portal on the Company_info layout, with the Relationship set to 'Allow creation of records' in the Contact_info table - then you can just select the blank Portal row to add the Contact information to the Contact table...

This will automatically populate the key field that links the Company in the Contact table.

[Fitch]

Maybe you've connected the wrong ID fields. You'd want to connect the CompanyID in the Company table to a Company ID in the Contact table -- NOT to the Contact ID!

As webko said, you'd enable "allow creation" on the contact side. Then when you enter contact info in the empty portal row, the Company ID is automatically populated in the contact table.

Typically, you wouldn't copy the company name into the contact table, since that would be redundant. But there are scenarios where you might want to copy information (e.g., company address on to an invoice). In which case you could either script it or use the field auto-entry options.

Have you played around with any of the starter solutions?

Welcome to FM Forums.

[M_ichel]

Yes but...

 

(Hi all!)

 

What if I want to be able to add a Contact and have that create a record in the Company (if it doesn't already exist)?

1. I set up a Company table with company_No as the key, defined as unique.
2. I set up a Contact table (with a unique contact_No, but that is not relevant) which contains, among others, a field that contains company_no.
3. I set up a relationship (one-to-many, from Company to Contact) in which I checked the "Allow creation of records in this table via this relationship" box on the Company side.
4. I go to a layout which "shows records from the Contact table";
5. I create a few Contacts, indicating Company_No that don't already exist in the Company table.
6. Nothing happens in the Company table.

Why???  How do the records that I want to "Allow" actually get created?

Edit:

Actually, I would want to be able to do this in batch mode:  import a file of contacts and have that create unique (if they don't already exist) records in a Company table.

Thanks!

Edited February 6, 2016 by M_ichel
Added details.

[webko]

Does the import data have an identifying Company ID? Do you have a sample file?

There's a few ways to skin this cat, depending on what the source data looks like.

 

[M_ichel]

Hi webco.  Thanks for the response.

By the way, my question really concerns stock market speculation.  I have transactions (one purchase or sale of a certain number of futures contracts). From these transactions, I want to deduce positions which represent a "summary" of a set of transactions.  For instance, one position might be made up of these transactions:  Buy 2 contracts, buy 2 more, buy 2 more, Sell 6 contracts.  (Or the reverse:  Sell 2, sell 2, Buy 4).  The transactions are time-stamped, in chronological order and each position is always closed (meaning that the "number of contracts balance" is zero) before a new one is opened.

I can download the transactions from my broker and I thought that I could somehow import them into a FM Pro file and through the magic of a one-to-many relationship that allows the creation of new records (on the "one" side of the equation) have the new position records automatically created...

But to tell you the truth, I now doubt whether this can be accomplished "automatically".  I'm looking at writing a script that would have the logic to 1- import the daily transactions and 2- create the position records.

To answer your question, the import data doesn't have an identifying Company ID Position Id:  I was thinking of just using an automatic serial number.

Comments?

[comment]

First, a record in one table will never be created automatically by entering data in another table. So your idea of importing the transactions into a Transactions table first, then running a script to create a record in Positions for each group and passing the PositionID value back to the group members is the correct approach. Unfortunately, you haven't told us how to identify transactions that belong to the same group.

 

[M_ichel]

Hi comment.  Thanks for the response.

OK.  So I was on the right track in thinking I would need a script.  [Sigh...]

Identifying the transactions that belong to the same position is simple: 

The transactions are all in chronological order.  Once a position has been opened (by it's first transaction), it will be closed (by its last transaction) before another position is opened.  Always.  Take a look at the attached file.

I will keep a running total of the number of contracts (if a "Buy", number of contracts is negative, if a "Sell", number of contracts is positive).  When a transaction makes this running total go to zero, that signals the end of a position.

IF Buy then

    Running_Total = Running_Total - Number_of_Contracts

ELSE

    Running_Total = Running_Total + Number_of_Contracts

ENDIF

IF Number_of_Contracts = 0 then  # we've reached the last transaction for this position

    Do what's needed to wrap up this position

ENDIF

END LOOP

etc.

 

2016-02-05 trades.xls

[comment]

37 minutes ago, M_ichel said:

When a transaction makes this running total go to zero, that signals the end of a position.

That's not going to be so simple. Have a look at the attached file. It does half of the work - and actually, you could possibly leave it at that. Properly, the existing relationship would be only temporary, and you would use it to populate the transactions with their parent's PositionID value - so that that the "real" relationship would be based on that.

 

Trades.fp7

Edited February 8, 2016 by comment

[M_ichel]

Hi Comment.  Thanks for the suggestions.

I had a script almost complete, ran into computer problems and had to spend time on that...

Using a script, I've pretty well achieved what I was looking for.

Thanks for your help!