How to reference different tables

[anonymous]

Hi there,

Im really struggling with this if anyone can help please.

I want to create the following.

data source
car catalogue

car make	car model	engine size
vw	golf	1.2
vw	golf	1.6
vw	polo	1
vw	polo	1.2

main dealer inventory

location	dealer	car make	car model
uk	john golf dealer	vw	golf
usa	dave polo seller	vw	polo

dealer customer allocation

location	dealer	engine size	owner
uk	john golf dealer	1.2	smith
uk	john golf dealer	1.6	green
usa	dave polo seller	1	jones
usa	dave polo seller	1.2	simpson

I dont want the main car dealer inventory to include the engine size but I want the car dealer to be able to add new entries for engine size per customer but only relevant for the car make and model that dealer is allowed to sell for.

I cant seem to work out how to do the right referencing.

Any suggestions would be most appreciative.

Thanks

John

[WillowMobileSys]

johnmcavoy:

but only relevant for the car make and model that dealer is allowed to sell for.

It’s not clear what this means but no matter.

I think you need to split the Dealer and Dealer Inventory into separate tables and need a Customer table. Your Dealer table will be a Parent table and then “Main Dealer Inventory” and “Dealer Customer Allocation” will be child tables. I also always recommend having a dedicated Key column in each table. So the tables would be as follows:

Dealer

• Dealer ID - assigned by UNIQUEID() function
• Name
• Location
• Others

Customer

• Customer ID - assigned by UNIQUEID() function
• Name
• Address
• Phone
• Others

Car Catalogue

• Catalogue ID - assigned by UNIQUEID() function
• Make
• Model
• Engine Size
• Others

Main Dealer Inventory

• Dealer Inventory ID - assigned by UNIQUEID() function
• Dealer - REF column to Dealer table
• Car - REF to Car Catalogue table
• Others

Dealer Customer Allocation

• Customer allocation ID - assigned by UNIQUEID() function
• Dealer - REF column to Dealer table
• Customer - REF column to Customer table
• Car - REF to Car Catalogue table
• Others

I hope this helps! Questions?

[anonymous]

Thanks for the response, I’ll give that a try.

I have one other question I think should be really easy but Im struggling to work out how to do it (again noob - sorry). I have read lots of help notes on this subject but not managed to either make them work for me or understood what to do.

I have created a table with one of the columns referencing another table, used the enumlist feature and ‘select all’ which has populated a cell with all the values with commas separating them, which I gather is normal behaviour (ideally I would like them on separate rows but this looks quite complicated).

One work around I want to try is to reference this cell with all the values in another table but select them as induvial values rather than all of them at once. I don’t know if this is either possible or how to do it. If you have any help that would be awesome.

Thanks

John

[WillowMobileSys]

I didn’t see this until now. Note: if you simply tap the Reply button at the bottom of the thread, no others posters are physically notified unless thaty ahve set the thread to be watched. Instead, to make sure you have responded to a particular person, tap the Reply button INSIDE of their post. That person will be notified of the response. alternatively you can add that person’s tag which is just “@” followed by their username like this @johnmcavoy.

So, forst, I’m curious how you are doing with your table structures?

Secondly, regarding this…

johnmcavoy:

I have created a table with one of the columns referencing another table, used the enumlist feature and ‘select all’ which has populated a cell with all the values with commas separating them, which I gather is normal behaviour (ideally I would like them on separate rows but this looks quite complicated).

It actually is not complicated. Most likely what you want to do is create a Parent/Child relationship which is quite easy. You simply need to make sure that the Child table has a column to identify which row is the parent which you likely have already. It just needs to be set as a REF column back to the Parent table. Then in that Parent column, that’s inside of the Child table simply set the “Is Part of” property on. This will automatically connect the two tables and provide a row-by-row Inline table for the Child rows inside of the Parent views. See image below.

Parent Detail View showing Main Categories as child Inline table with 4 rows

To get more details on how to set this up, scroll down to the section named “Expressing Ownership Between Tables” in the article below: