[Feature]: auto generate form from SQL query and identify foreign key constraints

[sumitsum]

Is there an existing issue for this?

• [X] I have searched the existing issues

Summary

Ref: https://community.appsmith.com/t/define-foreign-key-drop-downs/955 From community:

Given the following table in a postgresql database:

CREATE TABLE IF NOT EXISTS public.mf_shift_outputs
(
id bigint NOT NULL DEFAULT nextval(‘mf_shift_outputs_id_seq’::regclass),
mf_shift_id bigint NOT NULL,
stitem_id bigint NOT NULL,
uom_id bigint NOT NULL,
output numeric NOT NULL,
planned_time numeric NOT NULL,
run_time_speed numeric NOT NULL,
operators numeric NOT NULL,
work_order_id bigint,
usercompanyid bigint NOT NULL,
created timestamp without time zone DEFAULT now(),
createdby character varying COLLATE pg_catalog.“default”,
alteredby character varying COLLATE pg_catalog.“default”,
lastupdated timestamp without time zone DEFAULT now(),
CONSTRAINT mf_shift_outputs_pkey PRIMARY KEY (id),
CONSTRAINT mf_shift_outputs_shift_id_fkey FOREIGN KEY (mf_shift_id)
REFERENCES public.mf_shifts (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT mf_shift_outputs_stitem_id_fkey FOREIGN KEY (stitem_id)
REFERENCES public.st_items (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT mf_shift_outputs_uom_id_fkey FOREIGN KEY (uom_id)
REFERENCES public.st_uoms (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT mf_shift_outputs_usercompanyid_fkey FOREIGN KEY (usercompanyid)
REFERENCES public.system_companies (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT mf_shift_outputs_work_order_id_fkey FOREIGN KEY (work_order_id)
REFERENCES public.mf_workorders (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
)

Can Appsmith auto generate a form and resolve the foregn keys from the constraints so that, say, a description for a referenced id could be shown in a drop down?

I am guessing not because form generation from a query on the table presents the foreign key ‘lookups’ as id columns.

Thanks
Martyn

Why should this be worked on?

Makes it easier to create forms.

[dilippitchika]

@momcilo-appsmith this looks like a similar problem where the starting point is a CREATE query than a form or table

[ecmonsen]

This is a needed feature for me too.