create related record.

[grumbachr]

Here is my problem.

I have 2 tables right now that I need to have get a serial number from a 3rd table. I thought I could make a relationship where TableA and TableB could create records in TableC but its not working. Any suggestion on how to get this done.

I've tried GetNextSerialValue but it says i have too few parameters set and I have no idea what else to set in the calulation to get it to work.

If you need a better description let me know I'll try to explain myself better.

thanks in advance.

[Sanjai]

Some description would be helpful. Where you are calculating the getnextserialvalue? How the tables are joined?

[grumbachr]

Table_A is a device type and it has a Device ID field thats an auto enter serial number

Table_B is another type of equipment and has its on Device ID field.

In Table_C I've created 3 fields C_ID (primarykey-auto enter serial number) A_ID and B_ID. A_ID is related to ID from Table_A and B_ID is related to ID from Table_B. As I create a record in either Table A or B I want a record automatically created in Table_C which automatically but it is not working.

My goal is to use the primarykey ID from Table_C to create related records in Table_D which is used to track these device.

I am stumbling somewhere and can quite figure out how to do this with out scripts. I would like to avoid scripting this but if i have to I will.

any advice is appreciated.

[Sanjai]

Create a relationship AC(allowing to create records in the related table) from table A to table C using A_id and another relationship BC from table B to table C using the B_id.

In the layout in table A, show the field A_id by selecting the relationship from AC.

When a new record is created in Table A, you may have to execute the script with one script step:

setfield[AC:A_id; A_id]

The above script would automatically create a record in table C. Similarly you can create a record when a record is created in table B.

[T-Square]

It's still hard to figure out what you're trying to accomplish here, but it sounds like you're trying to do something via the data structure that needs to be done via the interface.

Perhaps you need to turn the problem upside down, and create your interface based on Table C, with a portal to table D based on a relationship from C_ID to D_FK_ID_C (which is the field in D that holds C_ID). The relationship should allow the creation of records in table D. Then, when you add data in the portal row, it automatically gets the current C ID. The IDs for ID_A and ID_B would then be dropdowns on this layout using value lists based on the appropriate tables.

[-Queue-]

And now T's trigger happy, trigger happy every day!

(Apologies to Al Yankovic)

[Sanjai]

Hi T,

I had the same question that probably creating a record in C should have created a record in table A and B. But then I thought of an example of dept and emp where an emp can work in more than one dept. In FileMaker Pro we would use the EMPDept table for user input but in any other database, we would create an employee form and clicking on a save button would save the information in the background to the EMPDept table.

[T-Square]

Queue-- my browser hung, and when I reloaded the page, it posted twice. Now, where's the delete button???

Sanjai-- If I were to try and deal with the problem you describe (thank god I don't), I would go a different route. I would separate the employee and department entry, and then create a Shift entity that would have Employee and Department attributes.

It's all about the interface.

David

[-Queue-]

T, I deleted it for you. When you click the Edit button, there is an option at the top of the screen to Delete Post. However, I am not certain if this is available to all users or only moderators. Either way, it's gone now, and only my trigger happy post remains. :wink2:

[Sanjai]

Hi David,

What do you mean by the shift entity. Isn't it the same what I referred in my post to use the EmpDept table with Employee and Department being a referential entity?

[grumbachr]

I think I've figured out what I needed. Now to just get it all working. I made a Join Table to bring all devices together.

So joining a User to DeviceType 1 or DeviceType 2 or Type 3 gives me a record in the jointable with a unique record ID.

This was the first step in getting what i wanted done.

Sorry that I didn't seem to explain myself very well but your comments did help to guide me in a different direction of thought.

[T-Square]

What do you mean by the shift entity. Isn't it the same what I referred in my post to use the EmpDept table with Employee and Department being a referential entity?

To my eye, when you say EmpDept, I see a single link between an employee and the department they work in--that is, Bob works in Sales. But the example you provided earlier (Bob working for Sales one day, and Shipping the next) contradicts that relationship. So, I suggested a Shift entity to represent the linking of one employee to one shift, working for one department. Then, when an employee works a shift, you can identify the department they are to be linked with for that shift.

Maybe we're barking up the same tree... ?

Cheers,

David

[Sanjai]

Yes, We are in the same page. However I considered it as a one to many relationship because I have developed a project wherein an employee can work in Sales and Training dept, can work in Recruiting and Training dept, can work partly in IT and in support dept.

You are right that probably Grumbachr should revise the database structure if he can.